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
It is our choices, that show what we truly are, far more than our abilities.
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>
GROUPBY 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 ?)
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.
PatriceSc
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.
It is our choices, that show what we truly are, far more than our abilities.
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
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?
Participant
1445 Points
2835 Posts
Result set shows wrong output while putting join using CTE
Jan 25, 2021 09:23 AM|demoninside9|LINK
Hi All,
I am getting the amount got by employee in every month for the selected year. below is my query
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
All-Star
48570 Points
18082 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 25, 2021 10:13 AM|PatriceSc|LINK
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):
Participant
1445 Points
2835 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 25, 2021 10:23 AM|demoninside9|LINK
Single year means? Please explain.
Every records saved with a date.
All-Star
48570 Points
18082 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 25, 2021 12:53 PM|PatriceSc|LINK
I meant that for now you are using
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:
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 ?)
Participant
1445 Points
2835 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 27, 2021 06:53 AM|demoninside9|LINK
yes, you are right. But for now mt table is having data only for 2020.
As u suggested, I tried to track the data by date. and changed my sp line below
But it shows are rows are with unique date.
Contributor
2900 Points
852 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 28, 2021 06:08 AM|Sean Fang|LINK
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:
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
All-Star
48570 Points
18082 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 28, 2021 12:27 PM|PatriceSc|LINK
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?
Contributor
2581 Points
1135 Posts
Re: Result set shows wrong output while putting join using CTE
Jan 29, 2021 09:00 AM|Shuvo Aymon|LINK
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.
If more posts give you useful answers, Please mark each as "Answer".