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

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

StackExchange Code Review Q#66934, answer score: 7