none
Get date wise sales and return table RRS feed

  • 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

    Tuesday, February 12, 2019 11:18 AM

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

    Tuesday, February 12, 2019 11:24 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

    Tuesday, February 12, 2019 11:24 AM
  • Thank you very much Visakh.. This is what exactly i want.
    Tuesday, February 12, 2019 11:54 AM