need help in sql query may be using union

Question

Hi Guys

I have a sql query where i have 6 identical columns and 4 non identical columns. Not sure how to proceed and join 4 cte's together . I was thinking Union All is the best option but couldn't figure out how to proceed.
This is my SQL Query. I have tried using full outer join but i am not convinced whether it is a right query or not.

declare @lwkstartdate as date
declare @lwkendate as date
declare @secondlastweekstartdate as date
declare @secondlastweekenddate as date
declare @thirdlastweekstartdate as date
declare @thirdlastweekenddate as date
declare @fourthlastweekstartdate as date
declare @fourthlastweekenddate as date

set @lwkstartdate=(select Week_Start_Date -7 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @lwkendate=(select week_end_date -7 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @secondlastweekstartdate=(select Week_Start_Date -14 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @secondlastweekenddate=(select week_end_date -14 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @thirdlastweekstartdate=(select Week_Start_Date -21 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @thirdlastweekenddate=(select week_end_date -21 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @fourthlastweekstartdate=(select Week_Start_Date -28 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))
set @fourthlastweekenddate=(select week_end_date -28 from dimcalendar where cast(date_fld as date)=cast(getdate() as date))

;with lwk as
(
select cast(h.StartDateTime as date) as TxnDate,p.code as SKU,p.name as SKU_Description, i.LineValue as SalesValue, i.Quantity as Qty,usp.[Supplier Name],usp.[Supplier Code]
,spi.WasPrice as WAS,spi.[Current Retail Price] as [NOW]
from Txn.Header h
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId
join dbo.ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId
join dbo.product p on p.pkProductID = pv.fkProductID
join dbo.ProductVariantExtendedCode pvec on pvec.fkProductVariantID = pv.pkProductVariantID
join dbo.ProductAttributeValue AS PA WITH (NOLOCK) ON PA.fkProductID = P.pkProductID
join dbo.ProductTypeAttribute AS pta ON pta.pkProductTypeAttributeID = PA.fkProductTypeAttributeID
JOIN dbo.AttributeType AS [at] ON [at].pkAttributeTypeID = pta.fkAttributeTypeID
join dbo.usvProductPrimarySupplier usp on usp.SKU = p.code
join dbo.PricingInfo1 spi with(nolock) on p.code=spi.SKU
where pa.value like 'VSO%' and cast(h.startdatetime as date) between cast(@lwkstartdate as date) and cast(@lwkendate as date)
),lwk_basedata
as
(
select SKU,[Supplier Name],SKU_Description,WAS,[NOW],sum(qty) as [Units Sold],[Supplier Code]
,sum(SalesValue) as [LWK]
from lwk
group by SKU,[Supplier Name],SKU_Description,WAS,[NOW],[Supplier Code]
),[lwk-2] as
(
select cast(h.StartDateTime as date) as TxnDate,p.code as SKU,p.name as SKU_Description, i.LineValue as SalesValue, i.Quantity as Qty,usp.[Supplier Name],usp.[Supplier Code]
,spi.WasPrice as WAS,spi.[Current Retail Price] as [NOW]
from Txn.Header h
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId
join dbo.ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId
join dbo.product p on p.pkProductID = pv.fkProductID
join dbo.ProductVariantExtendedCode pvec on pvec.fkProductVariantID = pv.pkProductVariantID
join dbo.ProductAttributeValue AS PA WITH (NOLOCK) ON PA.fkProductID = P.pkProductID
join dbo.ProductTypeAttribute AS pta ON pta.pkProductTypeAttributeID = PA.fkProductTypeAttributeID
JOIN dbo.AttributeType AS [at] ON [at].pkAttributeTypeID = pta.fkAttributeTypeID
join dbo.usvProductPrimarySupplier usp on usp.SKU = p.code
join dbo.SKUPricingInfo1 spi with(nolock) on p.code=spi.SKU
where pa.value like 'VSO%' and cast(h.startdatetime as date) between cast(@secondlastweekstartdate as date) and cast(@secondlastweekenddate as date)
),[lwk_basedata-2]
as
(
select SKU,[Supplier Name],SKU_Description,WAS,[NOW],sum(qty) as [Units Sold],[Supplier Code]
,sum(SalesValue) as [LWK-2]
from [lwk-2]
group by SKU,[Supplier Name],SKU_Description,WAS,[NOW],[Supplier Code]
),[lwk-3] as
(
select cast(h.StartDateTime as date) as TxnDate,p.code as SKU,p.name as SKU_Description, i.LineValue as SalesValue, i.Quantity as Qty,usp.[Supplier Name],usp.[Supplier Code]
,spi.WasPrice as WAS,spi.[Current Retail Price] as [NOW]
from Txn.Header h
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId
join dbo.ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId
join dbo.product p on p.pkProductID = pv.fkProductID
join dbo.ProductVariantExtendedCode pvec on pvec.fkProductVariantID = pv.pkProductVariantID
join dbo.ProductAttributeValue AS PA WITH (NOLOCK) ON PA.fkProductID = P.pkProductID
join dbo.ProductTypeAttribute AS pta ON pta.pkProductTypeAttributeID = PA.fkProductTypeAttributeID
JOIN dbo.AttributeType AS [at] ON [at].pkAttributeTypeID = pta.fkAttributeTypeID
join dbo.usvProductPrimarySupplier usp on usp.SKU = p.code
join dbo.SKUPricingInfo1 spi with(nolock) on p.code=spi.SKU
where pa.value like 'VSO%' and cast(h.startdatetime as date) between cast(@thirdlastweekstartdate as date) and cast(@thirdlastweekenddate as date)
),[lwk_basedata-3]
as
(
select SKU,[Supplier Name],SKU_Description,WAS,[NOW],sum(qty) as [Units Sold],[Supplier Code]
,sum(SalesValue) as [LWK-3]
from [lwk-3]
group by SKU,[Supplier Name],SKU_Description,WAS,[NOW],[Supplier Code]
),[lwk-4] as
(
select cast(h.StartDateTime as date) as TxnDate,p.code as SKU,p.name as SKU_Description, i.LineValue as SalesValue, i.Quantity as Qty,usp.[Supplier Name],usp.[Supplier Code]
,spi.WasPrice as WAS,spi.[Current Retail Price] as [NOW]
from Txn.Header h
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId
join dbo.ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId
join dbo.product p on p.pkProductID = pv.fkProductID
join dbo.ProductVariantExtendedCode pvec on pvec.fkProductVariantID = pv.pkProductVariantID
join dbo.ProductAttributeValue AS PA WITH (NOLOCK) ON PA.fkProductID = P.pkProductID
join dbo.ProductTypeAttribute AS pta ON pta.pkProductTypeAttributeID = PA.fkProductTypeAttributeID
JOIN dbo.AttributeType AS [at] ON [at].pkAttributeTypeID = pta.fkAttributeTypeID
join dbo.usvProductPrimarySupplier usp on usp.SKU = p.code
join dbo.SKUPricingInfo1 spi with(nolock) on p.code=spi.SKU
where pa.value like 'VSO%' and cast(h.startdatetime as date) between cast(@fourthlastweekstartdate as date) and cast(@fourthlastweekenddate as date)
),[lwk_basedata-4]
as
(
select SKU,[Supplier Name],SKU_Description,WAS,[NOW],sum(qty) as [Units Sold],[Supplier Code]
,sum(SalesValue) as [LWK-4]
from [lwk-4]
group by SKU,[Supplier Name],SKU_Description,WAS,[NOW],[Supplier Code]
)

select a.SKU,a.[Supplier Name],a.SKU_Description,a.was,a.now,a.[Units Sold],a.LWK,b.[LWK-2],c.[LWK-3],d.[LWK-4]
from lwk_basedata a
full outer join
[lwk_basedata-2] b
on a.[Supplier Code] = b.[Supplier Code]
full outer join
[lwk_basedata-3] c
on b.[Supplier Code] = c.[Supplier Code]
full outer join
[lwk_basedata-4] d
on c.[Supplier Code] = d.[Supplier Code]

I want my output to be in this format as u can see it in the screenshot

Any help will be appreciated

Kind regards
Farhan Jamil

Answer 1

It looks like your query can be solved with case based pivot using datepart(week,StartDateTime) as one of the group by columns. If you can post your main table structure with some data, we can provide an actual query.

Answer 2

Hi @Farhan Jamil
Check this query:

;with lwk as  
(  
select cast(h.StartDateTime as date) as TxnDate,p.code as SKU,p.name as SKU_Description  
      ,i.LineValue as SalesValue, i.Quantity as Qty, usp.[Supplier Name]  
	  ,usp.[Supplier Code] ,spi.WasPrice as WAS, spi.[Current Retail Price] as [NOW]  
	  ,h.startdatetime as date  
from Txn.Header h  
join dbo.vw_branch b on h.fkBranchId = b.pkBranchID  
join txn.ItemLine i on i.fkHeaderId = h.pkHeaderId  
join dbo.ProductVariant pv on pv.pkProductVariantID = i.fkProductVariantId  
join dbo.product p on p.pkProductID = pv.fkProductID  
join dbo.ProductVariantExtendedCode pvec on pvec.fkProductVariantID = pv.pkProductVariantID  
join dbo.ProductAttributeValue AS PA WITH (NOLOCK) ON PA.fkProductID = P.pkProductID  
join dbo.ProductTypeAttribute AS pta ON pta.pkProductTypeAttributeID = PA.fkProductTypeAttributeID  
JOIN dbo.AttributeType AS [at] ON [at].pkAttributeTypeID = pta.fkAttributeTypeID  
join dbo.usvProductPrimarySupplier usp on usp.SKU = p.code  
join dbo.PricingInfo1 spi with(nolock) on p.code=spi.SKU  
where pa.value like 'VSO%'   
),lwk_basedata as  
(  
select SKU,[Supplier Name],SKU_Description,WAS,[NOW]  
      ,sum(qty) as [Units Sold],[Supplier Code]  
      ,sum(case when date between cast(@lwkstartdate as date) and cast(@lwkendate as date) then SalesValue else null end) as [LWK]  
	  ,sum(case when date between cast(@secondlastweekstartdate as date) and cast(@secondlastweekenddate as date) then SalesValue else null end) as [LWK-2]  
	  ,sum(case when date between cast(@thirdlastweekstartdate as date) and cast(@thirdlastweekenddate as date) then SalesValue else null end) as [LWK-3]  
	  ,sum(case when date between cast(@fourthlastweekstartdate as date) and cast(@fourthlastweekenddate as date) then SalesValue else null end) as [LWK-4]  
from lwk  
group by SKU,[Supplier Name],SKU_Description,WAS,[NOW],[Supplier Code]  
)  
select SKU,[Supplier Name],SKU_Description,was,now,[Units Sold],LWK,[LWK-2],[LWK-3],[LWK-4]  
from lwk_basedata   

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.