LW, MTD, STD, YTD Sales... all in line and looking pretty 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.CalendarDatesLW
dbo.CalendarDatesMTD
dbo.CalendarDatesSTD
dbo.CalendarDatesYTD


So 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 AmountYTD
from 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.Category


That'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?
I think your problem is do with the amount of cartesian products you are forming. I would approach something like this, as I alluded to in chat: 

with salesYTD as (
    SELECT SalesPerson, InvoiceDate, Sum(Amount) As AmountOnDay
    FROM sales 
    WHERE InvoiceDate &gt; DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
    GROUP By SalesPerson, InvoiceDate
),
calculatedYTD as (
    SELECT SalesPerson, SUM(AmountOnDay)
    FROM salesYTD
    GROUP BY SalesPerson
),
calculatedMTD as (
    SELECT SalesPerson, SUM(AmountOnDay)
    FROM salesYTD
    WHERE InvoiceDate &gt;= DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
    GROUP BY SalesPerson
)
-- etc
SELECT *
FROM calculatedYTD ytd
LEFT JOIN calculatedMTD mtd ON ytd.SalesPerson = mtd.SalesPerson


I realise the above is not the same code as you posted, trying to show a basic principal, will expand if you want, just shout in comments

But the basic principle is:


Perform the expensive operation once, specifically the one with the biggest date range (YTD)
Perform the rest of the calculation from the result of the first one (its already filtered)


But there are a few things you can do, to which I have made the assumptions above, assuming:


YTD means the beginning of the year
MTD means the beginning of the month... etc...


you can:


Do away with the calculation functions (they are expensive compared to...)
Calculate your YTD as DATEADD(year, DATEDIFF(year, 0, GETDATE()), 0)
Calculate your MTD as DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)


I haven't run it, but I suspect that it will give you far better IO/performance. Let me know if it helps...