# 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?

• Is the date range for LW guaranteed to be a subset of YTD? Oct 17 '14 at 0:02
• I was going to answer yes, ...but week 1 is an exception.. so no :( Oct 17 '14 at 0:08

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       1
y                1       0       0       1


So, 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

• I like your answer over the others, because it's easier to maintain / change the groupings, or add columns - I might want to drill it down to model and material eventually, and this will be easier to work with. FWIW, the same 35 rows were returned in 2 seconds :) Oct 17 '14 at 14:27

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

• The problem is that the year isn't going by the "normal" Jan-Dec calendar - the function looks up dates in a dbo.Calendar table. Oct 16 '14 at 23:18
• @Mat'sMug I think Stuart's idea still applies -- instead of joining on a bunch of dates, just test if the date lies in the range [getStartOfYear(GETDATE()), GETDATE()]. Oct 16 '14 at 23:26
• That's cool, what is the start of the year and i'll alter the YTD Oct 17 '14 at 6:14

You're doing four joins which are identical except for the date range. That suggests that you ought to be working with the date ranges as a kind of data, rather than as code or as tables or as columns. Let's make that happen using some temporary tables:

create table #dateRanges
( Period NVARCHAR(3)
, StartDateIncl DATE
, EndDateExcl DATE
);

insert into #dateRanges values
( 'LW',  '2016-10-05', '2016-10-12' ),
( 'MTD', '2016-10-01', '2016-11-01' ),
( 'STD', '2016-09-01', '2017-01-01' ),
( 'YTD', '2016-01-01', '2017-01-01' );


For simplicity, I've hard-coded some date ranges. You could obviously generate the values using a function. Just the start and end dates should be enough; generating every date within the range is overkill, and just complicates the joins later.

With that, you should be able to run this simple query, which produces all the results you need, just not aggregated the way you want:

select range.Period,
sales.RepCode as SalesRep,
sales.Category,
sales.TotalUnits as Units,
sales.TotalNetAmount as Amount
from dbo.sales as sales
inner join #dateRanges as range
on sales.InvoiceDate >= StartDateIncl
and sales.InvoiceDate < EndDateExcl;


So, how do we get from there to your desired output? You want to make two columns for each time period. Making columns out of rows can be done using a PIVOT operation.

with salesDateRanges (Period, SalesRep, Category, Units, Amount) as (
select range.Period,
sales.RepCode as SalesRep,
sales.Category,
sales.TotalUnits as Units,
sales.TotalNetAmount as Amount
from dbo.sales as sales
inner join #dateRanges as range
on sales.InvoiceDate >= StartDateIncl
and sales.InvoiceDate < EndDateExcl
), pivotedByUnits (SalesRep, Category, LW, MTD, STD, YTD) as (
select SalesRep,
Category,
isnull(sum([LW]),  0),
isnull(sum([MTD]), 0),
isnull(sum([STD]), 0),
isnull(sum([YTD]), 0)
from salesDateRanges pivot (
sum(Units)
for Period IN ([LW], [MTD], [STD], [YTD])
) as pivotTable
group by SalesRep, Category
), pivotedByAmount (SalesRep, Category, LW, MTD, STD, YTD) as (
select SalesRep,
Category,
isnull(sum([LW]),  0),
isnull(sum([MTD]), 0),
isnull(sum([STD]), 0),
isnull(sum([YTD]), 0)
from salesDateRanges pivot (
sum(Amount)
for Period IN ([LW], [MTD], [STD], [YTD])
) as pivotTable
group by SalesRep, Category
)
select u.SalesRep,
u.Category,
u.LW  as UnitsLW,  a.LW  as AmountLW,
u.MTD as UnitsMTD, a.MTD as AmountMTD,
u.STD as UnitsSTD, a.STD as AmountSTD,
u.YTD as UnitsYTD, a.YTD as AmountYTD
from pivotedByUnits as u
inner join pivotedByAmount as a
on u.SalesRep = a.SalesRep
and u.Category = a.Category;


If you only want Sales on Dates which are actually in the Date-List function, you could try this:

We use the MIN/MAX Date of all Ranges as a preliminary filter (should especially speed things up if the data is partitioned on Date) And then just one Table-Range-Scan over all relevant entries and tally up all relevant sums...

You could also save MIN/MAX of the range in an external table, or just calculate them beforehand and use them as direct variables...

WITH alldates AS (
SELECT CalendarDate FROM dbo.CalendarDatesLW( getdate() )
UNION
SELECT CalendarDate FROM dbo.CalendarDatesMTD( getdate() )
UNION
SELECT CalendarDate FROM dbo.CalendarDatesSTD( getdate() )
UNION
SELECT CalendarDate FROM dbo.CalendarDatesYTD( getdate() )
),
maxdaterange AS (
SELECT  MIN( CalendarDate ) minStartDate
,MAX( CalendarDate ) maxEndDate
FROM  alldates
)
-- Everything above is only so we don't have to scan the whole table, but can profit from partitions or an Index-Range Scan on the table afterwards...
SELECT  sales.RepCode
,sales.Category
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesLW( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsLW
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesLW( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountLW
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesMTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsMTD
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesMTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountMTD
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesSTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsSTD
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesSTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountSTD
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesYTD( getdate() ) THEN sales.TotalUnits ELSE 0 END ) UnitsYTD
,SUM( CASE WHEN sales.InvoiceDate IN dbo.CalendarDatesYTD( getdate() ) THEN sales.TotalNetAmount ELSE 0 END ) AmountYTD
FROM  dbo.Sales
WHERE  sales.InvoiceDate >= ( SELECT minStartDate FROM maxdaterange )
AND  sales.InvoiceDate <= ( SELECT maxEndDate   FROM maxdaterange )
GROUP  BY sales.RepCode, sales.Category;

• The alldates cte could be simplified to SELECT CalendarDate FROM dbo.CalendarDatesLW(getdate()) UNION SELECT CalendarDate FROM dbo.CalendarDatesYTD(getdate()) Oct 17 '14 at 13:55
• Yes - in your case only LW could have a smaller minimum date, but the UNION should be called only once, so it would be minimal optimization and additional comments would be needed to explain. So I'd rather leave the more verbose version of getting the Min and Max of ALL Date-ranges Oct 20 '14 at 7:56