sql - 使用带有重置 SQL Server 2012 的运行总计的销售目标

Question

我正在使用 SQL Server 2012 为销售代理提取滚动销售信息。如果这些代理商在 6 天或更短时间内完成 15 笔销售，他们将获得奖金。如果他们达到目标，则滚动计数会重置。星期天应该被忽略。

因此给定以下 AgentID、日期和销售数据:

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

我预计目标被击中的日期是:

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

我尝试了几种方法，但找不到重置滚动总数的方法。

我认为窗口函数是可行的方法。

查看类似的帖子 Window Functions - Running Total with reset

我认为这与我需要的类似，但不能完全让它正常工作。

更新:我尝试的第一件事是创建滚动的 6 天窗口，但我没有看到它在基于集合的方法中起作用。我可以使用游标逐行浏览这些行，但我真的不喜欢这个主意。

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

然后我尝试使用上面 URL 中提到的脚本，但我不太明白它在做什么。我只是在改变一些事情，希望能找到解决方案，但那是行不通的。

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 1

那就对了!这是一个有趣的挑战，我很高兴我破解了它。注释等在代码注释中。如果您想更改累积奖金的天数，请更改 @DaysInBonusPeriod 中的值。这也适用于多个 AgentID 和任何日期序列，假设任何缺失的日期不包括在奖金累积期中 - 即:如果您忽略星期日 和 星期三周期是这样计算的:

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