12
\$\begingroup\$

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):

dbo.Sales is queried 4 times, execution plan looks like stalagmites, horizontally stacked

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?

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

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
\$\endgroup\$
  • 3
    \$\begingroup\$ 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 :) \$\endgroup\$ – Mathieu Guindon Oct 17 '14 at 14:27
7
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Mathieu Guindon Oct 16 '14 at 23:18
  • 2
    \$\begingroup\$ @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()]. \$\endgroup\$ – mjolka Oct 16 '14 at 23:26
  • \$\begingroup\$ That's cool, what is the start of the year and i'll alter the YTD \$\endgroup\$ – Stuart Blackler Oct 17 '14 at 6:14
5
\$\begingroup\$

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;
\$\endgroup\$
3
\$\begingroup\$

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;
\$\endgroup\$
  • 1
    \$\begingroup\$ The alldates cte could be simplified to SELECT CalendarDate FROM dbo.CalendarDatesLW(getdate()) UNION SELECT CalendarDate FROM dbo.CalendarDatesYTD(getdate()) \$\endgroup\$ – Mathieu Guindon Oct 17 '14 at 13:55
  • \$\begingroup\$ 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 \$\endgroup\$ – Falco Oct 20 '14 at 7:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.