Bob Bob - 28 days ago 7
SQL Question

Sales Targets using running totals with resets SQL Server 2012

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

Answer

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