Result set shows wrong output while putting join using CTE

Question

User1052662409 posted

Hi All,

I am getting the amount got by employee in every month for the selected year. below is my query

;WITH Tbl1 AS (
    SELECT CAST('2020-01-01' as datetime) as MONTH_NAME
    UNION ALL
    SELECT DATEADD(MONTH,1,MONTH_NAME)
    FROM Tbl1
    WHERE DATEPART(MONTH,MONTH_NAME) < 12
), TBL2 AS (
    SELECT  (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, tei.imp_fin_appr_date), -1) )) AS MONTH_NAME,
            ISNULL(SUM (tei.imp_amount_approved_by_finance),0) AS TOTAL_SALES, SUM(TE.audit_amount) AS Expenditure
    FROM tbl_emp_imprest TEI 
    INNER JOIN tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1
    GROUP BY MONTH(TEI.imp_fin_appr_date)
)


SELECT  DATENAME(MONTH,m.MONTH_NAME) as MONTH_NAME,
        ISNULL(t2.TOTAL_SALES,0) AS Got_Amount, ISNULL(t2.Expenditure,0) AS Expenditure
FROM Tbl1 m
LEFT JOIN TBL2 t2
    ON t2.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)  

The output is as below

The Got_amount is showing the double of actual amount and also Expenditure is not correct. What I am doing wrong? Although all where clauses are fine.

Please help

Answer 1

User753101303 posted

Hi,

For now my guess is:
- your query seems to assume you only have a single year in your source tables? Is this correct? My personal preference is to avoid this kind of shortcut (ie grouping only on the month) so that my query still works if this is no longer the case later
- or the INNER JOIN could perhaps introduce duplicates (for example because of a missing constraint)

My general approach for debugging this is to look at the underlying data I'm trying to group ie what if check that the following returns exactly the row you expect (possibly focusing on a single month for which you have wrong results):

SELECT  *
    FROM tbl_emp_imprest TEI 
    INNER JOIN tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1 -- AND DATEPPART(MONTH,tei.imp_fin_appr_date)=<some month>

Answer 2

User1052662409 posted

your query seems to assume you only have a single year in your source tables? Is this correct?

Single year means? Please explain.

Every records saved with a date.

Answer 3

User753101303 posted

I meant that for now you are using 

GROUP BY MONTH(TEI.imp_fin_appr_date) -- gives a month number between 1 and 12 regardless of the year

so if in your source table you have values from september 2019 to december 2020 you'll have both september 2019 and september 2020 on a single ,"September" row.

Anyway in my experience it's often worth to just spent a couple of minutes to see the exact problem you have so that you can then just fix the problem you found.

It's often quicker than trying to fix each and every problem that could possibly happen until to find which fix is solving your issue.

So once again if for example the "September" row is wrong I would start by looking at:

  SELECT  *
    FROM tbl_emp_imprest TEI 
    INNER JOIN tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1 AND MONTH(tei.imp_fin_appr_date)=9

to see which rows I don"t want and why they are taken (my guess being either you have a row for another year than 2020 or you have the same emp_id twice in a table causing the INNER JOIN to produce an unexpected extra row ?)

Answer 4

User1052662409 posted

gives a month number between 1 and 12 regardless of the year

yes, you are right. But for now mt table is having data only for 2020.

SELECT  *
    FROM tbl_emp_imprest TEI 
    INNER JOIN tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1 AND MONTH(tei.imp_fin_appr_date)=9

As u suggested, I tried to track the data by date. and changed my sp line below

;WITH Tbl1 AS (
    SELECT CAST('2020-01-01' as datetime) as MONTH_NAME
    UNION ALL
    SELECT DATEADD(MONTH,1,MONTH_NAME)
    FROM Tbl1
    WHERE DATEPART(MONTH,MONTH_NAME) < 12
), TBL2 AS (
    SELECT  (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, tei.imp_fin_appr_date), -1) )) AS MONTH_NAME,tei.imp_fin_appr_date as date1,
            ISNULL(SUM (tei.imp_amount_approved_by_finance),0) AS TOTAL_SALES, SUM(TE.audit_amount) AS Expenditure
    FROM tbl_emp_imprest TEI 
    INNER JOIN tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1 and MONTH(TEI.imp_fin_appr_date) =9
    GROUP BY MONTH(TEI.imp_fin_appr_date),tei.imp_fin_appr_date
)


SELECT  DATENAME(MONTH,m.MONTH_NAME) as MONTH_NAME, t2.date1,
        ISNULL(t2.TOTAL_SALES,0) AS Got_Amount, ISNULL(t2.Expenditure,0) AS Expenditure
FROM Tbl1 m
LEFT JOIN TBL2 t2
    ON t2.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)  

But it shows are rows are with unique date.

Answer 5

User-1330468790 posted

Hi demoninside9,

 

I count the number by hand and find the sum 'Got_Amout' - 27000 and the 'Expenditure' - 66000 are correct according to the detailed output.

I also construct a test using sql with below demo and the output is correct too.

Could you please provide us with a sample data which will produce a wrong output within above sql statement?

Sql Statement:

DROP TABLE IF EXISTS #tbl_emp_imprest 
DROP TABLE IF EXISTS #tblExpenditure 

CREATE TABLE #tbl_emp_imprest (
	imp_fin_appr_date DATETIME NOT NULL ,
	imp_amount_approved_by_finance INT NOT NULL,
	imp_id INT NOT NULL
)


INSERT INTO #tbl_emp_imprest VALUES('2020-06-03',9000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-08-04',40000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-08-17',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-08-23',30000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-03',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-04',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-07',30000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-11',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-14',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-16',30000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-19',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-22',30000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-25',20000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-28',40000,9)
INSERT INTO #tbl_emp_imprest VALUES('2020-09-30',20000,9)


CREATE TABLE #tblExpenditure  (
	audit_appr INT NOT NULL ,
	audit_amount INT NOT NULL,
	emp_id  INT NOT NULL
)

INSERT INTO #tblExpenditure VALUES(1,6000,9)

;WITH Tbl1 AS (
    SELECT CAST('2020-01-01' as datetime) as MONTH_NAME
    UNION ALL
    SELECT DATEADD(MONTH,1,MONTH_NAME)
    FROM Tbl1
    WHERE DATEPART(MONTH,MONTH_NAME) < 12
),TBL2 AS (
    SELECT  (DATENAME (MONTH, DATEADD ( MONTH, DATEPART(MONTH, tei.imp_fin_appr_date), -1) )) AS MONTH_NAME,
            ISNULL(SUM (tei.imp_amount_approved_by_finance),0) AS TOTAL_SALES, SUM(TE.audit_amount) AS Expenditure
    FROM #tbl_emp_imprest TEI 
    INNER JOIN #tblExpenditure TE
        ON TE.emp_id = TEI.imp_id
        where TEI.imp_id=9 AND Te.audit_appr=1
    GROUP BY MONTH(TEI.imp_fin_appr_date)
)

SELECT  DATENAME(MONTH,m.MONTH_NAME) as MONTH_NAME,
        ISNULL(t2.TOTAL_SALES,0) AS Got_Amount, ISNULL(t2.Expenditure,0) AS Expenditure
FROM Tbl1 m
LEFT JOIN TBL2 t2
    ON t2.MONTH_NAME = DATENAME(MONTH,m.MONTH_NAME)  

OUTPUT:

   

The output is working as expected since the data that I populate in the temp table is made based on your output.

 

Best regards,

Sean

Answer 6

User753101303 posted

Check other columns as well such as the emp_id (and all columns in general). At worst maybe a row for the same emp_id was registered twice in the db with a different date? (maybe a wrong month if you have a lower result than expected for another month?)

If you expect 60000 for Expenditure you have an extra row and the error on the other sum could help to find which row is not expected (for example if you expect 250000 rather than 270000 then the additional row shows 20000 in its Got_Amount column).

Edit: how do you know you have the wrong result? Could irt be that you have the correct result and that this your other source for this sum which is wrong?

Answer 7

User-1412735316 posted

Please share sample data in text format so that it's possible to populate same table and make sure the query is getting your expected result.