Bob - 9 months ago 44

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

Answer Source

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 `AgentID`

s 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
```

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