The way I like to think about CTEs is as tables you can build yourself in memory. If you can figure out a way to build it, you can do it with a CTE. Also, you can build multiple CTEs in one overall query and reference prior CTEs in subsequent ones. I never learned SQL in a proper setting, so I find CTEs to be the greatest things ever since I never learned subqueries. A CTE gives me a table I can structure myself and know what is in it so I can use it later. Here is an example of a CTE that I built once. I am sure there is probably a “better” way to do this, but since I never took any SQL classes I hack my way through with CTEs.
with CTE1(StartFP,StartFY,EndFP,EndFY)
as
(
select (case when FiscalPeriod - 6 < 1 then (12 + (FiscalPeriod - 6))
else FiscalPeriod - 6
end )as StartFP,
(case when FiscalPeriod - 6 < 1 then FiscalYear -1
else FiscalYear end) as StartFY,
FiscalPeriod as EndFP, FiscalYear as EndFY
from dbo.FiscalPer
where FiscalCalendarID = 'XXX' and StartDate <= GETDATE() and EndDate >= GETDATE()
),
CTE2 (StartDay)
as
(
select StartDate
from dbo.FiscalPer
where FiscalYear = (select StartFY from CTE1) and FiscalPeriod = (select StartFP from CTE1)
),
CTE3 (EndDay)
as
(
select EndDate
from dbo.FiscalPer
where FiscalYear = (select EndFY from CTE1) and FiscalPeriod = (select EndFP from CTE1)
),
CTE([Day],EndDay)
as
(
select StartDate as [Day], (select EndDay from CTE3) as EndDay
from dbo.FiscalPer
where FiscalCalendarID = 'XXX' and FiscalYear = (select StartFY from CTE1) and FiscalPeriod = (select StartFP from CTE1)
union all
select dateadd(d,1,[Day]), EndDay
from CTE
where [Day] +1 <= EndDay
),
CTE4 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
select CTE.[Day], FiscalPeriod as FP, FiscalYear as FY, 0 as [$ Sales], 0 as [$ GM], 0 as [$ Bookings], 0 as [$GM]
from CTE
left join FiscalPer as f
on CTE.[Day] >= f.StartDate and CTE.[Day] <= f.EndDate
where f.FiscalCalendarID = 'XXX'
),
CTE5 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
select CTE4.[Day], FiscalPeriod as FP, FiscalYear as FY, 0 as [$ Sales], 0 as [$ GM], 0 as [$ Bookings], 0 as [$GM]
from CTE4
left join FiscalPer as f
on CTE4.[Day] >= f.StartDate and CTE4.[Day] <= f.EndDate
where f.FiscalCalendarID = 'XXX'
union
select ih.InvoiceDate as [Day], ih.FiscalPeriod as FP, ih.FiscalYear as FY, SUM(id.ExtPrice) as [$ Sales],
SUM(id.ExtPrice) - (SUM((id.MtlUnitCost + id.LbrUnitCost + id.BurUnitCost + id.SubUnitCost + id.MtlBurUnitCost) * id.OurShipQty)) as [$ GM],
0 as [$ Bookings], 0 as [$GM]
from CTE4
join dbo.InvcHead as ih
on CTE4.[Day] = ih.InvoiceDate
join dbo.InvcDtl as id
on ih.Company = id.Company and ih.InvoiceNum = id.InvoiceNum
where ih.Company = 'XXX'
group by ih.InvoiceDate, ih.FiscalPeriod, ih.FiscalYear
union
select b.BookDate as [Day], CTE4.FP as FP, CTE4.FY as FY, 0 as [$ Sales], 0 as [$ GM], SUM(b.BookValue) as [$ Bookings],
SUM(b.BookValue) - SUM(b.SellingBookQty * (p.StdBurdenCost + p.StdLaborCost + p.StdMaterialCost + p.StdMtlBurCost + p.StdSubContCost))
from CTE4
join dbo.BookDtl as b
on CTE4.[Day] = b.BookDate
join dbo.OrderDtl as o
on b.Company = o.Company and b.OrderNum = o.OrderNum and b.OrderLine = o.OrderLine
join dbo.PartCost as p
on o.Company = p.Company and o.PartNum = p.PartNum
where b.Company = 'XXX'
group by b.BookDate, CTE4.FP, CTE4.FY
),
CTE6 ([Day],FP,FY,[$ Sales], [$ GM], [$ Bookings],[$GM])
as
(
select [Day], FP, FY, MAX([$ Sales]) as [$ Sales], MAX([$ GM]) as [$ GM], MAX([$ Bookings]) as [$ Bookings], MAX([$GM]) as [$GM]
from CTE5
group by [Day], FP, FY
)
select [Day], FP, FY, [$ Sales], [$ GM], (case when [$ GM] = 0 then 0 else [$ GM]/[$ Sales] end) as [%],
[$ Bookings], [$GM], (case when [$GM] = 0 then 0 else [$GM]/[$ Bookings] end) as [% ]
from CTE6
option (maxrecursion 500);