Jump to content

list all the customers from two tables

Senthilkumar

By Senthilkumar
in MySQL Help

 Share

Recommended Posts

Senthilkumar Advanced Member

Senthilkumar

Posted
Posted

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. 

 

 

Link to comment
https://forums.phpfreaks.com/topic/321691-list-all-the-customers-from-two-tables/
Share on other sites
Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted (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 by Senthilkumar
Barand Prolific Member

Barand

Posted
Posted

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.

Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted (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 by Senthilkumar
Barand Prolific Member

Barand

Posted
Posted

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.

image.png.6e276d1221a3f06edf9312b14fc09ed5.png          image.png.a2363fbc3dc133f2884b8ff527671db1.png

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.

Barand Prolific Member

Barand

Posted
Posted

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;

image.thumb.png.10c228724a2cdb854d52c39b2de8e176.png

  • 3 weeks later...
Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted

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?

Barand Prolific Member

Barand

Posted
Posted

I can't reproduce your count with data you provided 

mysql> SELECT COUNT(DISTINCT customerid)
    -> FROM machinemaster
    -> WHERE machinestatus = 'A';
+----------------------------+
| COUNT(DISTINCT customerid) |
+----------------------------+
|                       8169 |
+----------------------------+

 

Barand Prolific Member

Barand

Posted
Posted

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.

Danishhafeez Member

Danishhafeez

Posted
Posted

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

Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted
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

Senthilkumar Advanced Member

Senthilkumar

Posted
  • Author
Posted

Dear Mr.Barand,

 

On 6/16/2024 at 2:01 AM, Barand said:

image.png.6e276d1221a3f06edf9312b14fc09ed5.png          image.png.a2363fbc3dc133f2884b8ff527671db1.png

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.

Barand Prolific Member

Barand

Posted
Posted

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?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • 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.