Senthilkumar Posted June 14 Share Posted June 14 Dear Team, I have the table of Machine master branch-wise, Billing table branch-wise and the common customer master table. I want to display the billing summary branch-wise with all customers from both tables(Billing and Machine master). I am using the following query select Branch,Plant, CustomerID, Customername, CustomerCity, CustomerSegment, CustomerType, JanBilling, FebBilling,MarBilling,AprBilling,MayBilling,JunBilling,JulBilling,AugBilling,SepBilling,OctBilling,NovBilling,DecBilling from (SELECT b.branchName as Branch,c.plantName as Plant,d.CustomerID as CustomerID,d.CustomerName as Customername,d.CustomerCity as CustomerCity,d.CustomerSegment as CustomerSegment,d.CustomerType as CustomerType FROM sbms.machinemaster as a inner join sbms.customermaster as d on d.CustomerID = a.CustomerID inner join sbms.branch as b on b.branchcode = a.BranchCode inner join sbms.plant as c on c.branchID = b.branchID WHERE 1 and a.MachineStatus='A' group by a.CustomerID,b.branchcode order by b.branchName ASC) as machine left join (SELECT d.CustomerID as CustomerID, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END ) / 100000 AS JanBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END ) / 100000 AS FebBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END ) / 100000 AS MarBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END ) / 100000 AS AprBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END ) / 100000 AS MayBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END ) / 100000 AS JunBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END ) / 100000 AS JulBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END ) / 100000 AS AugBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END ) / 100000 AS SepBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END ) / 100000 AS OctBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END ) / 100000 AS NovBilling, SUM( CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END ) / 100000 AS DecBilling FROM sbms.billing as a inner join sbms.customermaster as d on d.CustomerID = a.sold_party inner join sbms.plant as b on b.plantCode = a.sales_office inner join sbms.branch as c on c.branchID = b.branchID WHERE 1 and a.sales_doc_type in('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') and a.division NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') group by a.sold_party, a.sales_office ) as bill using (CustomerID) For example, in the billing table sales office 809, I am getting 417 customer lists (SELECT * FROM sbms.billing where sales_office ='809' and sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') group by sold_party). in Customer master BranchCode 809, I am getting 620 customers (SELECT * FROM sbms.machinemaster where BranchCode='809' and MachineStatus='A' group by CustomerID). I want to display the customer's list who are not doing the billing. But I am not getting proper output. I uploaded my table dump on the below link https://drive.google.com/file/d/1X_TXl3uCcuyzCXrMgvfF7wpC3aF-YInL/view?usp=sharing Please help me to get my required output. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 14 Share Posted June 14 28 minutes ago, Senthilkumar said: I want to display the customer's list who are not doing the billing. Could you explain that requirement, particularly "who are not doing the billing"? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted June 14 Author Share Posted June 14 We have to check the machine master which customer is not listed on the billing result branch wise and display those customers as 0 billing done on the output report Quote Link to comment Share on other sites More sharing options...
Barand Posted June 14 Share Posted June 14 So you are only interested in the 203 customers that are in machinemaster but not in billing? Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted June 14 Author Share Posted June 14 (edited) No. It should display along with 417 customer which is listed from billing table. Note: Total is not 620. Because some new customers billing has been done which is not available in machine master. So first we have to list all the customers from billing and along with we have to display the customers from machine master who is not available on billing table ( not done single billing of any month). Edited June 14 by Senthilkumar Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted June 14 Author Share Posted June 14 Dear Mr Barand, Please help me Quote Link to comment Share on other sites More sharing options...
Barand Posted June 14 Share Posted June 14 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. Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted June 15 Author Share Posted June 15 (edited) Dear Mr.Barand, Sorry for the mistakes you found on the table. I overlooked that. Now that mistakes have been corrected and the new table dump has been uploaded the link below https://drive.google.com/file/d/1Cd6-PkwetPseNvt64JxA3oIC_p88vEs3/view?usp=sharing I tried the code below. I am getting the output sum values matching and I think all the customers list I am getting. SELECT machine.Branch, machine.CustomerID, machine.Customername, machine.CustomerSegment, machine.CustomerType, COALESCE(bill.JanBilling, 0) as JanBilling, COALESCE(bill.FebBilling, 0) as FebBilling, COALESCE(bill.MarBilling, 0) as MarBilling, COALESCE(bill.AprBilling, 0) as AprBilling, COALESCE(bill.MayBilling, 0) as MayBilling, COALESCE(bill.JunBilling, 0) as JunBilling, COALESCE(bill.JulBilling, 0) as JulBilling, COALESCE(bill.AugBilling, 0) as AugBilling, COALESCE(bill.SepBilling, 0) as SepBilling, COALESCE(bill.OctBilling, 0) as OctBilling, COALESCE(bill.NovBilling, 0) as NovBilling, COALESCE(bill.DecBilling, 0) as DecBilling FROM ( SELECT b.branchName as Branch, d.CustomerID as CustomerID, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType FROM sbms.machinemaster as a INNER JOIN sbms.customermaster as d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch as b ON b.branchcode = a.BranchCode WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID ORDER BY b.branchName ASC ) as machine LEFT JOIN ( SELECT a.sold_party as CustomerID, c.branchName as Branch, b.plantName as Plant, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM sbms.billing as a INNER JOIN sbms.plant as b ON b.plantCode = a.sales_office INNER JOIN sbms.branch as c ON c.branchID = b.branchID WHERE a.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND a.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY a.sold_party, c.branchID ORDER BY c.branchName, b.plantName ) as bill USING (CustomerID, Branch) UNION SELECT bill.Branch, bill.CustomerID, bill.Customername, bill.CustomerSegment, bill.CustomerType, COALESCE(bill.JanBilling, 0) as JanBilling, COALESCE(bill.FebBilling, 0) as FebBilling, COALESCE(bill.MarBilling, 0) as MarBilling, COALESCE(bill.AprBilling, 0) as AprBilling, COALESCE(bill.MayBilling, 0) as MayBilling, COALESCE(bill.JunBilling, 0) as JunBilling, COALESCE(bill.JulBilling, 0) as JulBilling, COALESCE(bill.AugBilling, 0) as AugBilling, COALESCE(bill.SepBilling, 0) as SepBilling, COALESCE(bill.OctBilling, 0) as OctBilling, COALESCE(bill.NovBilling, 0) as NovBilling, COALESCE(bill.DecBilling, 0) as DecBilling FROM ( SELECT b.branchName as Branch, d.CustomerID as CustomerID, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType FROM sbms.machinemaster as a INNER JOIN sbms.customermaster as d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch as b ON b.branchcode = a.BranchCode WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID ORDER BY b.branchName ASC ) as machine RIGHT JOIN ( SELECT a.sold_party as CustomerID, c.branchName as Branch, b.plantName as Plant, d.CustomerName as Customername, d.CustomerSegment as CustomerSegment, d.CustomerType as CustomerType, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, '%Y-%m') WHEN '2024-12' THEN gross_amount ELSE 0 END) / 100000 AS DecBilling FROM sbms.billing as a INNER JOIN sbms.plant as b ON b.plantCode = a.sales_office INNER JOIN sbms.branch as c ON c.branchID = b.branchID INNER JOIN sbms.customermaster as d ON d.CustomerID = a.sold_party WHERE a.sales_doc_type IN ('ZIEC', 'ZISC', 'ZEXS', 'ZSP', 'ZISS') AND a.product_div NOT IN ('X1', 'X3', 'X4', 'X5', 'X6', 'X7', 'X8', 'X9', 'XA', 'XB', 'XC', 'XD', 'XE', 'XG') GROUP BY a.sold_party, c.branchID ORDER BY c.branchName, b.plantName ) as bill USING (CustomerID, Branch) ORDER BY Branch, Customername; Can you please confirm whether the above code is correct or not, in the above code I want to filter the result branch-wise and plant-wise. Also, the right join query takes 5 to 6 seconds to execute. Can you please suggest me Edited June 15 by Senthilkumar Quote Link to comment Share on other sites More sharing options...
Barand Posted June 15 Share Posted June 15 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted June 16 Share Posted June 16 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; Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted July 3 Author Share Posted July 3 Dear Mr. Barand, Thank you for your suggestion, and I apologize for the delayed response. I tried your code, but I am only getting the output from the billing table's customer list, not from the machine master customer list. For example, I filtered only one branch for testing, Chennai (801 in b.sales_office and 5 in br.branchid). In the machine master, I have a list of 657 customers based on machinestatus = 'A'. However, I am only getting a list of 603 customers from the query output. Could you please help me resolve this issue? Quote Link to comment Share on other sites More sharing options...
Barand Posted July 3 Share Posted July 3 I can't reproduce your count with data you provided mysql> SELECT COUNT(DISTINCT customerid) -> FROM machinemaster -> WHERE machinestatus = 'A'; +----------------------------+ | COUNT(DISTINCT customerid) | +----------------------------+ | 8169 | +----------------------------+ Quote Link to comment Share on other sites More sharing options...
Barand Posted July 3 Share Posted July 3 You customermaster table isn't masterful enough - several used ids are not in there mysql> SELECT COUNT(DISTINCT m.customerid) as unmatched -> FROM machinemaster m -> LEFT JOIN -> customermaster c USING (customerid) -> WHERE m.machinestatus = 'A' -> AND c.customerid IS NULL; +-----------+ | unmatched | +-----------+ | 242 | +-----------+ Try using a LEFT JOIN to the customermaster at the bottom of my query in case the missing rows are amongst those. Quote Link to comment Share on other sites More sharing options...
Danishhafeez Posted July 4 Share Posted July 4 From your description, it appears that the issue lies in retrieving customers who are not present in the billing table but are in the machine master table. To achieve this, you should use a LEFT JOIN and then filter out the rows where the billing information (CustomerID) is NULL from the billing table. SELECT machine.Branch, machine.Plant, machine.CustomerID, machine.Customername, machine.CustomerCity, machine.CustomerSegment, machine.CustomerType, COALESCE(bill.JanBilling, 0) AS JanBilling, COALESCE(bill.FebBilling, 0) AS FebBilling, COALESCE(bill.MarBilling, 0) AS MarBilling, COALESCE(bill.AprBilling, 0) AS AprBilling, COALESCE(bill.MayBilling, 0) AS MayBilling, COALESCE(bill.JunBilling, 0) AS JunBilling, COALESCE(bill.JulBilling, 0) AS JulBilling, COALESCE(bill.AugBilling, 0) AS AugBilling, COALESCE(bill.SepBilling, 0) AS SepBilling, COALESCE(bill.OctBilling, 0) AS OctBilling, COALESCE(bill.NovBilling, 0) AS NovBilling, COALESCE(bill.DecBilling, 0) AS DecBilling FROM ( SELECT b.branchName AS Branch, c.plantName AS Plant, d.CustomerID AS CustomerID, d.CustomerName AS Customername, d.CustomerCity AS CustomerCity, d.CustomerSegment AS CustomerSegment, d.CustomerType AS CustomerType FROM sbms.machinemaster AS a INNER JOIN sbms.customermaster AS d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch AS b ON b.branchcode = a.BranchCode INNER JOIN sbms.plant AS c ON c.branchID = b.branchID WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID, b.branchcode ) AS machine LEFT JOIN ( SELECT d.CustomerID AS CustomerID, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, Used LEFT JOIN to join machine (from machinemaster) with bill (from billing). Included COALESCE to handle NULL values in billing amounts. Added WHERE bill.CustomerID IS NULL to filter out customers present in machinemaster but not in billing. Make sure to adjust date formats and other specifics (gross_amount division, etc.) based on your actual database schema and requirements. This query should give you a list of customers who are in the machinemaster table for a branch but do not have billing records in the specified months. Best Regard Danish Hafeez | QA Assistant ICTInnovations Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted July 4 Author Share Posted July 4 21 minutes ago, Danishhafeez said: SELECT machine.Branch, machine.Plant, machine.CustomerID, machine.Customername, machine.CustomerCity, machine.CustomerSegment, machine.CustomerType, COALESCE(bill.JanBilling, 0) AS JanBilling, COALESCE(bill.FebBilling, 0) AS FebBilling, COALESCE(bill.MarBilling, 0) AS MarBilling, COALESCE(bill.AprBilling, 0) AS AprBilling, COALESCE(bill.MayBilling, 0) AS MayBilling, COALESCE(bill.JunBilling, 0) AS JunBilling, COALESCE(bill.JulBilling, 0) AS JulBilling, COALESCE(bill.AugBilling, 0) AS AugBilling, COALESCE(bill.SepBilling, 0) AS SepBilling, COALESCE(bill.OctBilling, 0) AS OctBilling, COALESCE(bill.NovBilling, 0) AS NovBilling, COALESCE(bill.DecBilling, 0) AS DecBilling FROM ( SELECT b.branchName AS Branch, c.plantName AS Plant, d.CustomerID AS CustomerID, d.CustomerName AS Customername, d.CustomerCity AS CustomerCity, d.CustomerSegment AS CustomerSegment, d.CustomerType AS CustomerType FROM sbms.machinemaster AS a INNER JOIN sbms.customermaster AS d ON d.CustomerID = a.CustomerID INNER JOIN sbms.branch AS b ON b.branchcode = a.BranchCode INNER JOIN sbms.plant AS c ON c.branchID = b.branchID WHERE a.MachineStatus = 'A' GROUP BY a.CustomerID, b.branchcode ) AS machine LEFT JOIN ( SELECT d.CustomerID AS CustomerID, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-01' THEN gross_amount ELSE 0 END) / 100000 AS JanBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-02' THEN gross_amount ELSE 0 END) / 100000 AS FebBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-03' THEN gross_amount ELSE 0 END) / 100000 AS MarBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-04' THEN gross_amount ELSE 0 END) / 100000 AS AprBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-05' THEN gross_amount ELSE 0 END) / 100000 AS MayBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-06' THEN gross_amount ELSE 0 END) / 100000 AS JunBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-07' THEN gross_amount ELSE 0 END) / 100000 AS JulBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-08' THEN gross_amount ELSE 0 END) / 100000 AS AugBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-09' THEN gross_amount ELSE 0 END) / 100000 AS SepBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-10' THEN gross_amount ELSE 0 END) / 100000 AS OctBilling, SUM(CASE DATE_FORMAT(a.billing_date,'%Y-%m') WHEN '2024-11' THEN gross_amount ELSE 0 END) / 100000 AS NovBilling, SUM(CASE DATE_FORMAT(a.billing_date, please paste the full code for understand Quote Link to comment Share on other sites More sharing options...
Senthilkumar Posted July 4 Author Share Posted July 4 Dear Mr.Barand, On 6/16/2024 at 2:01 AM, Barand said: I corrected the highlighted data type. I will modify the crossed columns later because I need to alter them in many places. I added all the customers from the machinemaster to the customermaster. I used a LEFT JOIN on the customermaster at the bottom of the query. I have included the plant name in the query because I want to display the plant name as well. The new query is SELECT branchname , plantName , 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, p.plantName , 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 sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.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, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid; I executed the query for the CHENNAI branch, but I am not getting the proper output. I obtained a list of 851 customers for the CHENNAI branch from the query output. In the machine master, I got a list of 658 customers using the query: SELECT DISTINCT customerid FROM sbms.machinemaster WHERE machinestatus = 'A' and BranchCode='801'; Within this list of 658 customers, 71 customers are not listed in the query output. I don't know where the mistake is happening. I have uploaded the new data dump at the link below for your reference. https://drive.google.com/file/d/1HCq7djniswgS_yIB5_s1lqJ7UOc421Lr/view?usp=sharing I have been stuck with this problem for the last 3 days. Please help me get the proper output. Quote Link to comment Share on other sites More sharing options...
Barand Posted July 4 Share Posted July 4 Reloaded your data and ran this query below (for branch 801) to count the customers from each section of the UNION ... billing machine master not in billing SELECT count(distinct customerid) as total_custs , SUM(JanBilling <> '-') as from_billing , SUM(JanBilling = '-') as from_mach_mast FROM ( SELECT branchname , plantName , 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, p.plantName , 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 sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.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') AND br.branchcode = '801' GROUP BY branchname, customerid UNION SELECT m.branchname, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL AND m.branchcode='801' GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid ) data; giving these counts +-------------+--------------+----------------+ | total_custs | from_billing | from_mach_mast | +-------------+--------------+----------------+ | 844 | 609 | 235 | +-------------+--------------+----------------+ 1 row in set (2.95 sec) I also ran this one to get the counts for all branches SELECT branchname , SUM(JanBilling <> '-') as from_billing , SUM(JanBilling = '-') as from_mach_mast FROM ( SELECT branchname , plantName , 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, p.plantName , 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 sbms.billing b JOIN sbms.plant p ON b.sales_office = p.plantcode JOIN sbms.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, Null , m.customerid , NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL FROM sbms.machinemaster m LEFT JOIN sbms.billing b ON m.customerid = b.sold_party WHERE m.machinestatus = 'A' AND b.sold_party IS NULL GROUP BY branchcode, customerid ) bill LEFT JOIN sbms.customermaster USING (customerid) ORDER BY branchname, customerid ) data GROUP BY branchname; giving +--------------+--------------+----------------+ | branchname | from_billing | from_mach_mast | +--------------+--------------+----------------+ | AHMEDABAD | 624 | 282 | | BANGALORE | 464 | 246 | | BHUVANESHWAR | 245 | 197 | | CHENNAI | 609 | 235 | | COIMBATORE | 418 | 157 | | DELHI | 398 | 216 | | Export | 0 | 66 | | GUWAHATI | 328 | 156 | | HYDERABAD | 348 | 92 | | INDORE | 335 | 212 | | JAIPUR | 290 | 106 | | KOCHI | 266 | 104 | | KOLKATA | 385 | 112 | | KOLKATTA | 0 | 7 | | LUCKNOW | 276 | 128 | | MOHALI | 511 | 266 | | MUMBAI | 420 | 291 | | NAGPUR | 235 | 126 | | PATNA | 233 | 61 | | PROD | 0 | 11 | | Projects | 0 | 23 | | PUNE | 717 | 383 | | QUALITY | 0 | 2 | | RAIPUR | 200 | 56 | | RANCHI | 176 | 76 | | SCEN | 0 | 1 | | SCHWING | 0 | 11 | | VISAKAPATNAM | 241 | 76 | | XCMG | 0 | 1 | +--------------+--------------+----------------+ 29 rows in set (6.18 sec) So where are you getting your number 658 and 71 from? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.