Answered by:
Get date wise sales and return table

-
Hi,
I have sales table
create table #Sales (Name varchar(50),date_ date, SalesAmount decimal(18,3))
insert into #Sales values ('Name1','2019-01-01',100)
insert into #Sales values ('Name1','2019-01-01',300)
insert into #Sales values ('Name1','2019-01-04',200)and return table
create table #return (Name varchar(50),date_ date, ReturnAmount decimal(18,3))
insert into #return values ('Name1','2019-01-01',50)
insert into #return values ('Name1','2019-01-02',100)and i need result like below table.
if there is no entries in both table , we need to skip that date. how to query it?
Thanks
Kasim
Question
Answers
-
like this
;With NameDates AS ( SELECT Name,[date_] FROM #Sales UNION SELECT Name,[date_] FROM #Return ) SELECT m.Name,m.[date_], COALESCE(SalesAmount,0.00) AS SalesAmount, COALESCE(ReturnAmount,0.00) AS ReturnAmount FROM NameDates m LEFT JOIN ( SELECT Name,[date_],SUM(SalesAmount) AS SalesAmount FROM #Sales GROUP BY Name,[date_] )s ON s.Name = m.Name AND s.[date_] = m.[date_] LEFT JOIN ( SELECT Name,[date_],SUM(ReturnAmount) AS ReturnAmount FROM #Return GROUP BY Name,[date_] )r ON r.Name = m.Name AND r.[date_] = m.[date_]
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page- Marked as answer by Thangam - Kasim Tuesday, February 12, 2019 11:54 AM
All replies
-
like this
;With NameDates AS ( SELECT Name,[date_] FROM #Sales UNION SELECT Name,[date_] FROM #Return ) SELECT m.Name,m.[date_], COALESCE(SalesAmount,0.00) AS SalesAmount, COALESCE(ReturnAmount,0.00) AS ReturnAmount FROM NameDates m LEFT JOIN ( SELECT Name,[date_],SUM(SalesAmount) AS SalesAmount FROM #Sales GROUP BY Name,[date_] )s ON s.Name = m.Name AND s.[date_] = m.[date_] LEFT JOIN ( SELECT Name,[date_],SUM(ReturnAmount) AS ReturnAmount FROM #Return GROUP BY Name,[date_] )r ON r.Name = m.Name AND r.[date_] = m.[date_]
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page- Marked as answer by Thangam - Kasim Tuesday, February 12, 2019 11:54 AM
-