Report

[KAVIYA]

1.Production Target and Production Actual was attached the below SQL query.

SELECT Month,Name, ProductionTotal, ProductionActual FROM 
( select count(a.id) as ProductionActual, Month, Fname from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual 

LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-01' group by Month, EmpID
 ) ProductionTotal USING (Month)

The output was executed for Production Target and Actual

2.Customer Target and Customer Actual was attached the below the SQL query.

 

SELECT Month,Name,CustomerTotal,CustomerActual FROM 
( select count(a.id) as CustomerActual, Month, Fname  from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND  (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual 

LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-04' group by Month, EmpID
 ) CustomerTotal USING (Month)

The output was executed for Customer Target and Actual

3.Marketing Target and Marketing Actual was attached the below the SQL query. It was not executed the query exact result.

 

SELECT Month,Fname,MarketingActual FROM 
( select count(a.id) as MarketingActual, Month, Fname  from sbms.marketing_data as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where b.Branch = '5'  AND (b.Role='4' OR b.Role='5')  AND Month='2024-01' group by Month, EmpID) MarketingActual 

LEFT JOIN (select count(a.id) as MarketingTotal, Month, Fname as Name from sbms.marketing_target as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where b.Branch = '5'  AND (b.Role='4' OR b.Role='5')  AND Month='2024-01' group by Month, EmpID) MarketingTotal USING (Month)

4.If i joining two table Prodution and customer table output will be executed only for production query not in customer query.

 

SELECT Month, Name, ProductionTotal, ProductionActual, CustomerTotal, CustomerActual FROM 
( select count(a.id) as ProductionActual, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual 

LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-01' group by Month, EmpID
 ) ProductionTotal USING (Month, Name)
 
LEFT JOIN ( select count(a.id) as CustomerActual, Month, Fname as Name  from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND  (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' group by Month, EmpID) CustomerActual USING (Month, Name)

LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-04' group by Month, EmpID
 ) CustomerTotal USING (Month, Name)
 

5. Final output i want production ,customer,Marketing table within single query.

[Barand]

You stand a better chance of help if

• you tell us exactly what your problem is
• we know what your data looks like (table structure and test data export dump)
• we know what output you want.

[KAVIYA]

• I'm getting results from the customer and production tables, but when I combine these two queries, I don't get any results from the tables.

[KAVIYA]

 

Dear Mr.Barand,

Thanks for your response for my post.

I am writing the query to get the output of actual and target of three category from different tables.

When I am writing query separately each category I am getting output. But when i am joining those queries, I am getting errors.

For example,

the below query to get the target and actual of machine details.

SELECT Month,Name, ProductionTotal, ProductionActual FROM 
( select count(a.id) as ProductionActual, Month, Fname from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') AND Month='2024-01' group by Month, EmpID) ProductionActual 

LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-01' group by Month, EmpID
) ProductionTotal USING (Month)

I am getting the below output.

Next when i am writing the below query to get the output if customer target and actual.

SELECT Month,Name,CustomerTotal,CustomerActual FROM 
( select count(a.id) as CustomerActual, Month, Fname as Name   from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND  (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04'
 group by Month, EmpID) CustomerActual 

LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-04' group by Month, EmpID
 ) CustomerTotal USING (Month, Name)

I am getting the below output.

When i am joining the above two query i am getting the category output only production output is not coming. Which ever is first, that output only coming.

the join query is.

SELECT Month,Name,CustomerTotal,CustomerActual,  ProductionTotal, ProductionActual FROM 
( select count(a.id) as CustomerActual, Month, Fname as Name   from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND  (VisitType = 'No Due' OR VisitDate !='') AND Month='2024-04' 
group by Month, EmpID) CustomerActual 

LEFT JOIN (select count(a.id) as CustomerTotal, Month, Fname as Name from sbms.customerdata as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-04' group by Month, EmpID
 ) CustomerTotal USING (Month, Name)
 
LEFT JOIN ( select count(a.id) as ProductionActual, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5') AND NOT (MCubicmeter = '0' AND MHourmeter ='0') 
AND Month='2024-01' group by Month, EmpID) ProdAct  USING (Month, Name)

LEFT JOIN (select count(a.id) as ProductionTotal, Month, Fname as Name from sbms.production as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where a.Branch = '5'  AND (b.Role='4' OR b.Role='5')   AND Month='2024-01' group by Month, EmpID
 ) ProductionTotal USING (Month, Name)

and the join query output is

single table is using to get the target and actual using above query

But i am using two tables to get the target and actual for the third category marketing.

Marketing query is

 

SELECT Month,Fname,MarketingActual,MarketingTotal FROM 
( select count(a.id) as MarketingActual, Month, Fname  from sbms.marketing_data as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where b.Branch = '5'  AND (b.Role='4' OR b.Role='5')  AND Month='2024-01' group by Month, EmpID) MarketingActual 

LEFT JOIN (select count(a.id) as MarketingTotal, Month, Fname as Name from sbms.marketing_target as a
inner join sbms.usertable as b on b.EmpNo = a.EmpID
where b.Branch = '5'  AND (b.Role='4' OR b.Role='5')  AND Month='2024-01' group by Month, EmpID) MarketingTotal USING (Month)

and the output is

But the marketing output should come like below image

 

I need to combine all these three categories and the final output should come like below.

Al my dumps are attached with the blow link for your reference.

https://drive.google.com/file/d/1pRranTejVygkSaufLxjIbiiFdfyxlSDq/view?usp=sharing

Please help me to solve this.

 

Edited March 3 by KAVIYA

[Barand]

It helps if all the months that you searching for are the same.

I can't see any data for 2024-04 in marketing_data table, so it gives this when I combine all

[Barand]

By the way @KAVIYA, I think you should get together with @Senthilkumar and join forces.

His database name, column names and query contents and use of varchar for almost everything bear an amazing similarity to yours.

[Senthilkumar]

Dear Mr. Barand,

We both are working together. I am in personal emergency leave. So informed her to post the query here. 

 

[Senthilkumar]

6 hours ago, Barand said:

I can't see any data for 2024-04 in marketing_data table, so it gives this when I combine all

ok

[Senthilkumar]

Dear mr. Barand,

Please help to solve the issue

[Barand]

I thought I had. Read my replies, which were...

• You are joining on "month" so if different subqueries are selecting different months, then they aren't going to match.
• Your marketing table is missing 2024-04 data so you aren't going to get any totals from that table for that month.

Do you need me to spell it out in more detail and make it clearer?

I had a go at rewriting your query with simple subquery for each total

SELECT ca.Month
     , Name
     , CustomerTotal
     , CustomerActual
     , ProductionTotal
     , ProductionActual
     , MarketingTotal
     , MarketingActual
FROM (
        SELECT 
               empNo as empId
             , fname as name
        FROM usertable
        WHERE role IN (4, 5)
     ) u
JOIN (
        SELECT 
               count(*) as CustomerTotal
              , sum(VisitType = 'No Due' OR VisitDate != '') as CustomerActual 
              , month
              , empid
              , branch
        FROM customerdata
        GROUP BY month,branch,empid
     ) ca USING (empid)
LEFT JOIN (
        SELECT 
               count(*) as ProductionTotal
             , sum(MCubicmeter OR MHourmeter) as ProductionActual  
             , month
             , empid
             , branch
        FROM production
        GROUP BY month, branch, empid
     ) pa USING (month, branch, empid)
LEFT JOIN (
        SELECT 
               count(*) as MarketingTotal
             , month
             , empid
             , branch
        FROM marketing_target
        GROUP BY month, branch, empid
     ) mt USING (month, branch, empid)
LEFT JOIN (
        SELECT 
               count(*) as MarketingActual
             , month
             , empid
        FROM marketing_data
        GROUP BY month, empid
     ) ma USING (month, empid)
WHERE ca.branch = '5'
      AND ca.month = '2024-04'
;     

 

[Senthilkumar]

Dear Mr.Barand,

The above query is working.

I have another query that is used for another user. 

SELECT EmpID, Month, ProductionActual,CustomerActual, MarketingTotal,MarketingActual FROM
(select count(*) as ProductionActual, Month, EmpID  from sbms.production WHERE EmpID='OWC10148' AND NOT (MCubicmeter = '0' AND MHourmeter ='0') group by Month) ProductionActual 
LEFT JOIN ( select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='OWC10148' AND (VisitType != 'Not Visited') group by Month) CustomerActual USING (Month)
LEFT JOIN (select count(*) as MarketingTotal, Month from sbms.marketing_target WHERE EmpID='OWC10148' group by Month ) marketing_target USING (Month)
LEFT JOIN (select count(*) as MarketingActual, Month from sbms.marketing_data WHERE EmpID='OWC10148' AND Date!=''  group by Month ) MarketingActual USING (Month)

The output of this query is 

 

In this, I am having value CustomerActual is 3 for the month of 2024-03. But the output is not displaying here because ProductionActual is not there for the month of 2024-03. If am given any value for ProductionActual on 2024-03, then the third row (2024-03) is displayed on my output. 

Please suggest me.

[Senthilkumar]

As per your code the below output is coming

But there is the data available for Production Actual & Total for 2024-02 month for Ragul M

I want the output should show if any one of the field data is submitted.

Edited March 9 by Senthilkumar

[Barand]

As the data I was given has no data for RAGUL's empno in any tables (except usertable) I cannot verify any of my theories on what may be wrong.

Also for branch 2 I have only this...

+---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+
| month   | name          | CustomerTotal | CustomerActual | ProductionTotal | ProductionActual | MarketingTotal | MarketingActual |
+---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+
| 2024-03 | Palanikumar B |               |                |                 |                  | 1              |                 |
| 2024-02 | NAVEENKUMAR P |               |                |                 |                  | 1              |                 |
+---------+---------------+---------------+----------------+-----------------+------------------+----------------+-----------------+

For branch 5...

+---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+
| month   | name             | CustomerTotal | CustomerActual | ProductionTotal | ProductionActual | MarketingTotal | MarketingActual |
+---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+
| 2024-02 | Nethaji.MK       |               |                |                 |                  | 1              |                 |
| 2024-02 | T.Anandakrishnan |               |                |                 |                  | 1              |                 |
| 2024-02 | Sivanraj.A       |               |                |                 |                  | 1              |                 |
| 2024-02 | kannan.r         |               |                |                 |                  | 1              |                 |
| 2024-01 | thayyanayaki     | 5             | 5              | 2               | 2                |                |                 |
| 2024-02 | thayyanayaki     | 5             | 5              | 2               | 2                |                |                 |
| 2024-03 | thayyanayaki     | 5             | 5              | 2               | 2                | 1              |                 |
| 2024-04 | thayyanayaki     | 5             | 3              | 2               | 1                |                |                 |
| 2024-01 | Vignesh.M        |               |                |                 |                  | 1              | 1               |
| 2024-02 | Vignesh.M        |               |                |                 |                  | 1              |                 |
| 2024-02 | N.Manikandan     |               |                |                 |                  | 1              |                 |
| 2024-02 | Dinesh.C         |               |                |                 |                  | 1              |                 |
+---------+------------------+---------------+----------------+-----------------+------------------+----------------+-----------------+

The previous queries (your and mine) would only report on whatever empno, branch and month values existed in the production table (the one that isn't LEFT JOINED). This version removes that reliance ...

SELECT u.Month
     , Name
     , CustomerTotal
     , CustomerActual
     , ProductionTotal
     , ProductionActual
     , MarketingTotal
     , MarketingActual
FROM (
        WITH RECURSIVE allmonths (id, month) as
        (
            SELECT 1, '2024-01'
            UNION ALL
            SELECT id+1, concat('2024-', lpad(id+1,2,'0'))
            FROM allmonths WHERE id < 4
        )
        SELECT 
               empNo as empId
             , fname as name
             , month
             , ? as branch
        FROM usertable, allmonths
        -- WHERE role IN (4, 5)
     ) u
LEFT JOIN (
        SELECT 
               count(*) as CustomerTotal
              , sum(VisitType = 'No Due' OR VisitDate != '') as CustomerActual 
              , month
              , empid
              , branch
        FROM customerdata
        GROUP BY month,branch,empid
     ) ca  ON u.month = ca.month AND u.empid = ca.empid AND u.branch = ca.branch
LEFT JOIN (
        SELECT 
               count(*) as ProductionTotal
             , sum(MCubicmeter OR MHourmeter) as ProductionActual  
             , month
             , empid
             , branch
        FROM production
        GROUP BY month, branch, empid
     ) pa  ON u.month = pa.month AND u.empid = pa.empid AND u.branch = pa.branch
LEFT JOIN (
        SELECT 
               count(*) as MarketingTotal
             , month
             , empid
             , branch
        FROM marketing_target
        GROUP BY month, branch, empid
     ) mt  ON u.month = mt.month AND u.empid = mt.empid AND u.branch = mt.branch
LEFT JOIN (
        SELECT 
               count(*) as MarketingActual
             , month
             , empid
        FROM marketing_data
        GROUP BY month, empid
     ) ma  ON u.month = ma.month AND u.empid = ma.empid AND u.branch = mt.branch
HAVING CustomerTotal OR CustomerActual OR ProductionTotal OR ProductionActual OR MarketingTotal OR MarketingActual 
ORDER BY u.empid, u.month;