Last post Sep 30, 2020 07:23 PM by imapsp
Sep 30, 2020 07:02 PM|demoninside9|LINK
I am trying to fetch the records by joining two tables for all the employees.
Below is my query
SELECT (select name from tbl_Employee where sno=TEI.imp_id) AS Name, (SELECT Location FROM tbl_Projects where Sno=TEI.imp_emp_location) AS Location, SUM(TEI.imp_amount_approved_by_finance) AS Imprest, SUM(TE.amount) AS Amount, SUM(TE.reco_amount) AS Reco, SUM (TE.approved_amount) AS Approved,SUM(TE.audit_amount) AS Audit
FROM tbl_emp_imprest TEI
LEFT JOIN tblExpenditure TE ON
WHERE TEI.imp_amount_approved_by_finance>0 AND
DATEPART(year,TEI.imp_fin_appr_date) =DATEPART(YEAR,GETDATE()) AND DATEPART(month,TEI.imp_fin_appr_date) =DATEPART(Month,GETDATE()) AND DATEPART(year,TE.date) =DATEPART(YEAR,GETDATE()) AND DATEPART(month,TE.date) =DATEPART(Month,GETDATE())
GROUP BY TEI.imp_id,TEI.imp_emp_location,TEI.imp_amount_approved_by_finance,TE.amount,TE.reco_amount,TE.approved_amount
The result of the above query is as below with duplicate rows.
Whereas I was willing to get the result like below.
Name Location Imprest Amount Reco Approved Audit
R Gupta Dumpsite Auditor 35000 5500 0 0 0
Why it is making the duplicate records and not summing the amount where I have used the SUM()?
Sep 30, 2020 07:23 PM|imapsp|LINK
Try removing columns TEI.imp_amount_approved_by_finance, TE.amount, TE.reco_amount and TE.approved_amount from Group By:
GROUP BY TEI.imp_id, TEI.imp_emp_location
Hope this help