That's Last Week, Month-to-Date, Season-to-Date, and Year-to-Date sales.I have a table-valued function for each one, that takes a date and returns all CalendarDate values for the interesting time span:dbo.CalendarDatesLWdbo.CalendarDatesMTDdbo.CalendarDatesSTDdbo.CalendarDatesYTDSo I have a CTE for selecting YTD sales (where year runs from December to November):with     cteYTD (SalesRep, Category, Units, Amount) as (        select             sales.RepCode,            sales.Category,            sum(sales.TotalUnits),            sum(sales.TotalNetAmount)        from dbo.Sales sales            inner join dbo.CalendarDatesYTD(getdate()) calendar on sales.InvoiceDate = calendar.CalendarDate        group by            sales.RepCode,            sales.Category),..then another for LW sales:    cteLW (SalesRep, Category, Units, Amount) as (        select             sales.RepCode,            sales.Category,            sum(sales.TotalUnits),            sum(sales.TotalNetAmount)        from dbo.Sales sales            inner join dbo.CalendarDatesLW(getdate()) lw on sales.InvoiceDate = lw.CalendarDate        group by            sales.RepCode,            sales.Category),...another for MTD sales:    cteMTD (SalesRep, Category, Units, Amount) as (        select             sales.RepCode,            sales.Category,            sum(sales.TotalUnits),            sum(sales.TotalNetAmount)        from dbo.Sales sales            inner join dbo.CalendarDatesMTD(getdate()) mtd on sales.InvoiceDate = mtd.CalendarDate        group by            sales.RepCode,            sales.Category),...and another for STD sales:    cteSTD (SalesRep, Category, Units, Amount) as (        select             sales.RepCode,            sales.Category,            sum(sales.TotalUnits),            sum(sales.TotalNetAmount)        from dbo.Sales sales            inner join dbo.CalendarDatesSTD(getdate()) std on sales.InvoiceDate = std.CalendarDate        group by            sales.RepCode,            sales.Category)..and they all individually run pretty much instantly (0-1 seconds each, execution plan isn't complaining about any would-be-missing index). Then I join them:select    ytd.SalesRep,    ytd.Category,    isnull(lw.Units,0) UnitsLW,    isnull(lw.Amount,0) AmountLW,    isnull(mtd.Units,0) UnitsMTD,    isnull(mtd.Amount,0) AmountMTD,    isnull(std.Units,0) UnitsSTD,    isnull(std.Amount,0) AmountSTD,    ytd.Units UnitsYTD,    ytd.Amount AmountYTDfrom cteYTD ytd    left join cteSTD std on ytd.SalesRep = std.SalesRep                        and ytd.Category = std.Category    left join cteMTD mtd on ytd.SalesRep = mtd.SalesRep                        and ytd.Category = mtd.Category    left join cteLW lw on ytd.SalesRep = lw.SalesRep                        and ytd.Category = lw.CategoryThat's 17 seconds, for 35 rows - and it gets exponentially worse if I start adding more columns in the group by ...and the execution plan looks very, very weird (although not surprising, given the script):I'm querying dbo.Sales 4 times, grabbing a different time span each time, and then aggregating the same fields over and over. There has to be a better way of doing this... right?dbo.Sales is a view, joining multiple FK's and implementing some business rules (e.g. such or such customer sales under category X should be reported under category Y).My gut-feeling is that I'm abusing CTE's and should be using something else to do this. But what?
LW, MTD, STD, YTD Sales... all in line and looking pretty
sql;sql server;finance;t sql;join
There is a flaw in your logic for the first week of the year. If a sale happens for a repcode/category in the last week of the previous year, but a sale for that same repcode/category has not yet happened in the current year, then the LW value will not be represented in the final result because it will not successfully join in the left outer join. You can possibly resolve this with a full outer join, but that gets really messy.A much simpler, and probably faster, solution is to build up just a single CTE of date factors, for all dates, something like:with DateParts as (    select CalendarDate, 1 as LWFact, 0 as MTDFact, 0 as STDFact, 0 as YTDFact    from dbo.CalendarDatesLW(GetDate())  UNION    select CalendarDate, 0 as LWFact, 1 as MTDFact, 0 as STDFact, 0 as YTDFact    from dbo.CalendarDatesMTD(GetDate())  UNION    select CalendarDate, 0 as LWFact, 0 as MTDFact, 1 as STDFact, 0 as YTDFact    from dbo.CalendarDatesSTD(GetDate())  UNION    select CalendarDate, 0 as LWFact, 0 as MTDFact, 0 as STDFact, 1 as YTDFact    from dbo.CalendarDatesYTD(GetDate())), DateFactors as (    select CalendarDate,           max(LWFact) as LWFact,           max(MTDFact) as MTDFact,           max(STDFact) as STDFact,           max(YTDFact) as YTDFact    from DateParts    group by CalendarDate)OK, now with the above data, if you consider a date that happened recently, but not last week, call it x, and another date y that happened last week, but before this month, and this season, but still this year, then the DateFactors table will look like:CalndarDate LWFact MTDFact STDFact YTDFact----------- ------ ------- ------- -------x                0       1       1       1y                1       0       0       1So, something that happened on date x will contribute to the MTD, STD, and YTD figures, but not the LW Figures.Now, you complete the query with:    select         sales.RepCode,        sales.Category,        sum(sales.TotalUnits * LWFact) as LWUnits,        sum(sales.TotalNetAmount * LWFact) as LWAmount,        sum(sales.TotalUnits * MTDFact) as MTDUnits,        sum(sales.TotalNetAmount * MTDFact) as MTDAmount,        sum(sales.TotalUnits * STDFact) as STDUnits,        sum(sales.TotalNetAmount * STDFact) as STDAmount,        sum(sales.TotalUnits * YTDFact) as YTDUnits,        sum(sales.TotalNetAmount * YTDFact) as YTDAmount    from dbo.Sales sales        inner join DateFactors on sales.InvoiceDate = DateFactors.CalendarDate    group by        sales.RepCode,        sales.Category