Thursday, December 28, 2017

How to combine results of multiples queries into a single dataset (by using CTE)

I have a requiremnet in which  I need to show the weekly,monthly and yearly sale.
the problem is that I need to combine all three dataset in to one so here is the trick we can use with keyword
this is demo script with following result:







with t1 as (
select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased in last 7 days' ,       
              SUM(A.total_amount) AS 'Total sales price purchased in the last 7 days'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(week, -1, Getdate())
   
 group by    E.product_name),

t2 as(
 select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased in last 30 days' ,       
              SUM(A.total_amount) AS 'Total sales price purchased in the last 30 days'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(year, -1, Getdate())
   
 group by    E.product_name
 ),
 t3 as(
 select           
             E.product_name AS 'Product name',
              sum( B.Qty )                                       AS 'Total grams purchased YTD' ,       
              SUM(A.total_amount) AS 'Total sales price purchased YTD'
              , row_number() over (order by product_name) rn
    FROM       A         
    INNER JOIN B         
    ON         (A.customer_id = B.customer_id       
               AND   A.ShopPurchaseStatus_ID=2 AND A.A_id = B.A_id )       
    INNER JOIN C         
    ON         (C.customer_id = B.customer_id       
               AND        C.shop_product_id = B.shop_product_id AND C.Valuepack_Type_ID=2 )       
         
     INNER JOIN D         
    ON        ( C.Reference_Number=D.LEOGold_By_Reference_Number
AND D.Customer_ID=C.Customer_ID) 
    INNER JOIN E         
    ON   D.shop_product_id = E.shop_product_id 
 WHERE A.Entry_Datetime >=   Dateadd(month, -1, Getdate())
   
 group by    E.product_name
 )

 select t3.[Product name], t1.[Total grams purchased in last 7 days],t1.[Total sales price purchased in the last 7 days],t2.[Total grams purchased in last 30 days],t2.[Total sales price purchased in the last 30 days],
 t3.[Total grams purchased YTD],t3.[Total sales price purchased YTD]
from t1 full outer join t2 on t1.rn = t2.rn
full outer join t3 on t2.rn = t3.rn


Reference:
https://stackoverflow.com/questions/15230350/how-to-combine-results-of-two-queries-into-a-single-dataset

No comments:

Post a Comment