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
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