Get date wise sales and return table

Question

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

Answer 1

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

Answer 2

Thank you very much Visakh.. This is what exactly i want.