Sales Targets using running totals with resets SQL Server 2012

Question

I'm using SQL Server 2012 to extract rolling sales info for sales agents. These agents are paid bonuses if they achieve 15 sales within a rolling time period of 6 or less days. If they make target, the rolling count resets. Sundays should be ignored.

So given the following AgentID, Date and Sales data:

SELECT 1 AgentID,'2016-10-31' Date,1 Sales
INTO #Sales
UNION SELECT 1,'2016-11-01',2
UNION SELECT 1,'2016-11-02',1
UNION SELECT 1,'2016-11-03',5
UNION SELECT 1,'2016-11-04',3
UNION SELECT 1,'2016-11-05',2
UNION SELECT 1,'2016-11-07',6
UNION SELECT 1,'2016-11-08',5
UNION SELECT 1,'2016-11-09',4
UNION SELECT 1,'2016-11-10',6
UNION SELECT 1,'2016-11-11',1
UNION SELECT 1,'2016-11-12',3
UNION SELECT 1,'2016-11-14',2
UNION SELECT 1,'2016-11-15',2
UNION SELECT 1,'2016-11-16',4
UNION SELECT 1,'2016-11-17',2
UNION SELECT 1,'2016-11-18',2

The Dates that I expect the target to be hit are:

2016-11-07 (period 2016-11-01 -> 2016-11-07)
2016-11-10 (period 2016-11-08 -> 2016-11-10)
2016-11-18 (period 2016-11-12 -> 2016-11-18)

AgentID Date    Sales   Qualify
-------------------------------
1   2016-10-31  1   0
1   2016-11-01  2   0
1   2016-11-02  1   0
1   2016-11-03  5   0
1   2016-11-04  3   0
1   2016-11-05  2   0
1   2016-11-07  6   1
1   2016-11-08  5   0
1   2016-11-09  4   0
1   2016-11-10  6   1
1   2016-11-11  1   0
1   2016-11-12  3   0
1   2016-11-14  2   0
1   2016-11-15  2   0
1   2016-11-16  4   0
1   2016-11-17  2   0
1   2016-11-18  2   1

I've tried a few approaches but I can't find a way to reset the rolling totals.

I think that window functions are the way to go.

Looking at posts like Window Functions - Running Total with reset

I think this is similar to what I need but can't quite get it to work correctly.

UPDATE: The first thing I tried was to create rolling 6 day windows but I don't see this working in a set-based approach. I could use a cursor to step through these rows but I really don't like the idea.

SELECT DATEADD(DAY,-6,a.Date) StartDate,Date EndDate,a.AgentID,a.Sales,
(SELECT SUM(b.Sales)
    FROM cteSales b
    WHERE b.Date <= a.Date
    AND b.Date >= DATEADD(DAY,-6,a.Date)) TotalSales
 FROM cteSales a

I then tried to use the script mentioned in the URL above but I don't really understand what it's doing. I'm just changing things in the hope of stumbling across the solution and that just isn't working.

WITH c1 as
(
  select *,
    sum(sales) over(order by IDDate rows unbounded preceding) as rt
  from cteSales
)

SELECT date, sales, rt,
  SalesTarget_rt - lag(SalesTarget_rt, 1, 0) over(order by date) as SalesTarget,
  rt * SalesTarget_rt as new_rt

from c1
  cross apply(values(case when rt >= 15 then 1 else 0 end)) as a1(SalesTarget_rt);

Also, does the sales after the 15 in the same day count towards the next target? So if they sold 14 on day 1 and then 3 on day 2 does the count towards the next target start at 2 or 0 on day 3? — iamdave, Nov 09 '16 at 13:41
Oh and also(!) is there always only going to be a max of one record per day for each Agent? — iamdave, Nov 09 '16 at 13:57
Thanks for taking a look iamdave, Additional sales over 15 do not carry over to the next day. The day after the target is reached always starts at 0. And yes, there will only ever be one record per agent per day. — Bob, Nov 10 '16 at 07:17

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Right then! This was a fun challenge and I am very chuffed that I cracked it. Notes etc are in the code comments. If you want to change the number of days within which a bonus can be accrued, change the value in @DaysInBonusPeriod. This also works for multiple AgentIDs and any sequence of dates, assuming that any missing dates are not included in the bonus accrual period - ie: If you ignore Sunday and Wednesday the period is counted thus:

Day       Period Day
Monday    1
Tuesday   2
Thursday  3
Friday    4
Saturday  5
Monday    6

Solution

declare @t table(AgentID int
                ,DateValue Date
                ,Sales int
                );
insert into @t                  
select 1,'2016-10-31',1 union all
select 1,'2016-11-01',2 union all
select 1,'2016-11-02',1 union all
select 1,'2016-11-03',5 union all
select 1,'2016-11-04',3 union all
select 1,'2016-11-05',2 union all
select 1,'2016-11-07',6 union all
select 1,'2016-11-08',5 union all
select 1,'2016-11-09',4 union all
select 1,'2016-11-10',6 union all
select 1,'2016-11-11',1 union all
select 1,'2016-11-12',3 union all
select 1,'2016-11-14',2 union all
select 1,'2016-11-15',2 union all
select 1,'2016-11-16',4 union all
select 1,'2016-11-17',2 union all
select 1,'2016-11-18',2 union all

select 2,'2016-10-31',1 union all
select 2,'2016-11-01',7 union all
select 2,'2016-11-02',0 union all
select 2,'2016-11-03',0 union all
select 2,'2016-11-04',0 union all
select 2,'2016-11-05',0 union all
select 2,'2016-11-07',0 union all
select 2,'2016-11-08',0 union all
select 2,'2016-11-09',1 union all
select 2,'2016-11-10',3 union all
select 2,'2016-11-11',2 union all
select 2,'2016-11-12',3 union all
select 2,'2016-11-14',7 union all
select 2,'2016-11-15',6 union all
select 2,'2016-11-16',3 union all
select 2,'2016-11-17',5 union all
select 2,'2016-11-18',3;

-- Set the number of days that sales can accrue towards a Bonus.
declare @DaysInBonusPeriod int = 6;

with rn -- Derived table to get incremental ordering for recursice cte.  This is useful as Sundays are ignored.
as
(
    select t.AgentID
            ,t.DateValue
            ,t.Sales
            ,row_number() over (order by t.AgentID, t.DateValue) as rn
    from @t t
)
,prev   -- Using the row numbering above, find the number of sales in the day before the bonus accrual period.  We have to use the row numbers as Sundays are ignored.
as
(
        select t.AgentID
                ,t.DateValue
                ,t.Sales
                ,t.rn
                ,isnull(tp.Sales,0) as SalesOnDayBeforeCurrentPeriod
        from rn t
            left join rn tp
                on(t.AgentID = tp.AgentID
                    and tp.rn = t.rn - @DaysInBonusPeriod       -- Get number of sales on the day before the max Bonus period.
                    )
)
,cte    -- Use a recursive cte to calculate running totals based on sales, whether the bonus was achieved the previous day and if the previous bonus was more than 5 days ago.
as
(
    select rn
            ,AgentID
            ,DateValue
            ,Sales
            ,SalesOnDayBeforeCurrentPeriod
            ,Sales as TotalSales
            ,case when Sales >= 15 then 1 else 0 end as Bonus
            ,1 as DaysSinceLastBonus

    from prev
    where rn = 1    -- Select just the first row in the dataset.

    union all

    select t.rn
            ,t.AgentID
            ,t.DateValue
            ,t.Sales
            ,t.SalesOnDayBeforeCurrentPeriod

            -- If the previous row was for the same agent and not a bonus, add the day's sales to the total, subtracting the sales from the day before the 6 day bonus period if it has been more than 6 days since the last bonus.
            ,case when t.AgentID = c.AgentID
                then case when c.Bonus = 0
                        then t.Sales + c.TotalSales - case when c.DaysSinceLastBonus >= @DaysInBonusPeriod then t.SalesOnDayBeforeCurrentPeriod else 0 end
                        else t.Sales
                        end
                else t.Sales
                end as TotalSales

            -- If the value in the TotalSales field above is 15 or more, flag a bonus.
            ,case when
                    case when t.AgentID = c.AgentID                                                                                                             --\ 
                    then case when c.Bonus = 0                                                                                                                  -- \
                            then t.Sales + c.TotalSales - case when c.DaysSinceLastBonus >= @DaysInBonusPeriod then t.SalesOnDayBeforeCurrentPeriod else 0 end  --  \ Same statement
                            else t.Sales                                                                                                                        --  / as TotalSales
                            end                                                                                                                                 -- /
                    else t.Sales                                                                                                                                --/
                    end >= 15
                then 1
                else 0
                end as Bonus

            -- If there is no flag in Bonus field above, increment the number of days since the last bonus.
            ,case when 
                case when                                                                                                                                           --\
                        case when t.AgentID = c.AgentID                                                                                                             -- \
                        then case when c.Bonus = 0                                                                                                                  -- |
                                then t.Sales + c.TotalSales - case when c.DaysSinceLastBonus >= @DaysInBonusPeriod then t.SalesOnDayBeforeCurrentPeriod else 0 end  -- | 
                                else t.Sales                                                                                                                        --  \ Same statement
                                end                                                                                                                                 --  / as Bonus
                        else t.Sales                                                                                                                                -- |
                        end >= 15                                                                                                                                   -- |
                    then 1                                                                                                                                          -- /
                    else 0                                                                                                                                          --/
                    end = 0
                then c.DaysSinceLastBonus + 1
                else 0
                end as DaysSinceLastBonus
            
    from prev t
        inner join cte c
            on(t.rn = c.rn+1)
)
select AgentID
        ,DateValue
        ,Sales
        ,TotalSales
        ,Bonus
from cte
order by rn
option (maxrecursion 0);

Great work iamdave. I thought it was complex and looking at the code, even more than I realised I'm getting a recursive limit on the CTEs but I'm sure I can get around that, either by increasing the limit or breaking it down into smaller chucks. I'll do some more testing on my live data but it looks like it will work. — Bob, Nov 10 '16 at 13:14
@Bob No worries :) Just add the `maxrecursion` option at the end, per my updated script. `0` lets it run until the end of the data and any non-zero value lets it run until that number of recursions and throws an error if it is hit. — iamdave, Nov 10 '16 at 13:21

Sales Targets using running totals with resets SQL Server 2012

1 Answers1

Solution