Jump to content

Barand

Moderators
  • Posts

    24,389
  • Joined

  • Last visited

  • Days Won

    803

Barand last won the day on June 7

Barand had the most liked content!

About Barand

Profile Information

Recent Profile Visitors

96,744 profile views

Barand's Achievements

Prolific Member

Prolific Member (5/5)

2.1k

Reputation

471

Community Answers

  1. My version, once I'd unravelled your multiple keys (eg branchID & branchCode) on tables and weird naming conventions like "b.plantCode = a.sales_office". (More bruises on my forehead) I am all for single letter table aliases but please make them easier ("b" for billing, "p" for plant, "c" for customerMaster etc) and use two letters if there is a clash (such as "br" for branch). It makes it a lot easier to follow than just a, b, c, d, e regardless of the table name. SELECT branchname , CustomerID , Customername , CustomerSegment , CustomerType , COALESCE(bill.JanBilling, '-') as JanBilling , COALESCE(bill.FebBilling, '-') as FebBilling , COALESCE(bill.MarBilling, '-') as MarBilling , COALESCE(bill.AprBilling, '-') as AprBilling , COALESCE(bill.MayBilling, '-') as MayBilling , COALESCE(bill.JunBilling, '-') as JunBilling , COALESCE(bill.JulBilling, '-') as JulBilling , COALESCE(bill.AugBilling, '-') as AugBilling , COALESCE(bill.SepBilling, '-') as SepBilling , COALESCE(bill.OctBilling, '-') as OctBilling , COALESCE(bill.NovBilling, '-') as NovBilling , COALESCE(bill.DecBilling, '-') as DecBilling FROM ( SELECT br.branchname , b.sold_party as customerid , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling , SUM(CASE DATE_FORMAT(billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM billing b JOIN plant p ON b.sales_office = p.plantcode JOIN branch br ON p.branchid = br.branchid WHERE b.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND b.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY branchname, customerid UNION SELECT m.branchname , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM machinemaster m LEFT JOIN billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchname, customerid ) bill JOIN customermaster USING (customerid) ORDER BY branchname, customerid;
  2. I have crossed out columns that don't belong in the tables beacause they are dependent on foreign keys to other tables (such as customer, branch, product etc). I also highlighted those with incorrect types. It's indexing that greatly improves query performance. Fields used in joins should be indexed as should columns frequently used in where clauses. I'll continue to work on your query now.
  3. Sorry, my head's hurting after banging it repeatedly aginst the wall in frustration after having looked at your table structures. Foe example... Columns which you are joining on, like CustomerID, are not indexed. ID columns, which (strangely) you are not using for your joins, are indexed twice (primary key and you also create another key on the same column). The gross_amount column that you are totalling is a varchar column!!! CustomerName, which belongs only in the customer table is repeated in billing and machinemaster tables. NORMALIZE!. And that's before I can comprehend what you want to do.
  4. I know this isn't a CSS solution but, when in doubt, cheat. I used an SVG image. Fonts will differ I I don't know which you used. <body> <header style='text-align: center'> <svg width='90%' viewBox='0 0 500 100' style='max-width: 800;'> <defs> <style type='text/css'> #main { font-family: "arial black"; font-size: 20pt; font-weight: 800; fill: #000; } #better { font-family: "segoe script"; font-size: 40pt; font-weight: 600; fill: #80FF80; } </style> </defs> <text x='338' y='65' id='better'>better</text> <text x='12' y='60' id='main'>Making every day taste</text> </svg> </header> </body> screen width: 1024 screen width: 450
  5. So you are only interested in the 203 customers that are in machinemaster but not in billing?
  6. Could you explain that requirement, particularly "who are not doing the billing"?
  7. Design the data model Create the tables Populate tables with test data And one thing I forgot to mention earlier... RTFM
  8. How did you learn advanced SQL? With practice. How did you start tinkering with data? By designing and building databases to support applications I needed to develop, then developing them using the database. Problem is I've no data to tinker with. Then create some. (See tutorials link in my signature)
  9. I have no idea how your chart software works but if you provide five data values (8, 19, 11, 3, 49) for one type and three data values (1, 3, 7) for the other, how is it supposed to know which months those values are for? You have a very weird x-axis for that chart (the month sequence is 6-5-4-3-2-1-12-11-10-9-8-7). The norm is to put them in chronological order.
  10. Barand

    CBT Exam

    Another option, maybe Create question in MSWord Save page as html doc Display it in an <iframe> on your page
  11. Why don't you group by job type, month
  12. Barand

    CBT Exam

    If you save "X.doc", which contains an equation, as "X.html" into a "documents" folder it creates as subfolder "X_files" containing the equation as an image documents | +- X.html +- X_files | +- image1.jpg
  13. Have you triedd using xpath()? <?php $str = '<xml><chapter num="17"> <verse num="1">And after six days Jesus taketh Peter, James, and John his brother, and bringeth them up into an high mountain apart,</verse> <verse num="2">And was transfigured before them: and his face did shine as the sun, and his raiment was white as the light.</verse> <verse num="3">And, behold, there appeared unto them Moses and Elias talking with him.</verse> <verse num="4">Then answered Peter, and said unto Jesus, Lord, it is good for us to be here: if thou wilt, let us make here three tabernacles; one for thee, and one for Moses, and one for Elias.</verse> <verse num="5">While he yet spake, behold, a bright cloud overshadowed them: and behold a voice out of the cloud, which said, This is my beloved Son, in whom I am well pleased; hear ye him.</verse> <verse num="6">And when the disciples heard <i>it,</i> they fell on their face, and were sore afraid.</verse> <verse num="7">And Jesus came and touched them, and said, <span class="j">Arise, and be not afraid. </span></verse> <verse num="8">And when they had lifted up their eyes, they saw no man, save Jesus only.</verse> <verse num="9">And as they came down from the mountain, Jesus charged them, saying, <span class="j">Tell the vision to no man, until the Son of man be risen again from the dead. </span></verse> <verse num="10">And his disciples asked him, saying, Why then say the scribes that Elias must first come?</verse> <verse num="11">And Jesus answered and said unto them, <span class="j">Elias truly shall first come, and restore all things. </span></verse> <verse num="12"><span class="j">But I say unto you, That Elias is come already, and they knew him not, but have done unto him whatsoever they listed. Likewise shall also the Son of man suffer of them. </span></verse> <verse num="13">Then the disciples understood that he spake unto them of John the Baptist.</verse> <verse num="14">And when they were come to the multitude, there came to him a <i>certain</i> man, kneeling down to him, and saying,</verse> <verse num="15">Lord, have mercy on my son: for he is lunatick, and sore vexed: for ofttimes he falleth into the fire, and oft into the water.</verse> <verse num="16">And I brought him to thy disciples, and they could not cure him.</verse> <verse num="17">Then Jesus answered and said, <span class="j">O faithless and perverse generation, how long shall I be with you? how long shall I suffer you? bring him hither to me. </span></verse> </chapter></xml> '; $xml = simplexml_load_string($str); $verses = $xml->xpath('//verse'); ?> <!DOCTYPE html> <html lang='en'> <head> <title>XML example</title> <meta charset='utf-8'> <style type='text/css'> .j { color: blue; font-style: italic; font-weight: 600; } </style> </head> <body> <?php foreach ($verses as $v) { echo "<p>{$v->asXML()}</p>"; } ?> </body> </html> Gives...
  14. Open your browser's develop tools. Click on "Network" tab Click on one of the rows to view its headers FYI, when using date(), the default time is the current time, so this will suffice... <?= date("d-M-Y H:i:s") ?> BST
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.