有条件运行tally sql server

Question

我打篮球，想在特定条件下得分。我从不在周六或周日打球，尽管我并不总是每周都打球。

如果我错过了一周的训练，我的记录会重置。如果我输了，我的拉力赛就会重新开始。如果我在周五获胜，并在下周一获胜，我的记录应该会继续。

以下是一个示例结果集：

|       Date | Points | Won_Flag | Tally |
|------------|--------|----------|-------|
| 2015-07-23 |     18 |    false |     0 |
| 2015-07-24 |      7 |     true |     7 |
| 2015-07-28 |     10 |     true |    10 |
| 2015-07-29 |     20 |     true |    30 |
| 2015-07-30 |     11 |    false |     0 |
| 2015-07-31 |     18 |     true |    18 |
| 2015-08-01 |     31 |     true |    31 |
| 2015-09-14 |      0 |    false |     0 |
| 2015-09-15 |      8 |     true |     8 |
| 2015-09-16 |     13 |    false |     0 |
| 2015-09-17 |      9 |     true |     9 |
| 2015-09-18 |      1 |     true |    10 |
| 2015-09-21 |      7 |     true |    17 |
| 2015-09-22 |     15 |    false |     0 |
| 2015-10-01 |      9 |    false |     0 |
| 2015-10-02 |      5 |     true |     5 |
| 2015-10-05 |     14 |     true |    19 |
| 2015-10-13 |      3 |     true |     3 |
| 2015-10-14 |     14 |     true |    17 |
| 2015-10-15 |     12 |     true |    29 |
| 2015-10-16 |      8 |     true |    37 |

SQL Fiddle

Answer 1

您可以使用累积和和lag()来完成此操作。使用lag()确定是否需要重新启动计数。然后累加该标志以获得可用于累积和的组标识符：

select t.*, sum(tally) over (partition by grp order by date) as cumeTally
from (select t.*, sum(tallyStartFlag) over (order by date) as grp
      from (select t.*,
                   (case when lag(date) over (order by date)) = datedd(day, -1, date) and
                              datename(weekday, date) in ('Tuesday', 'Wednesday', 'Thursday', 'Friday')
                         then 0
                         when lag(date) over (order by date) = dateadd(day, -3, date) and
                              datename(weekday, date) = 'Monday'
                         then 0
                         else 1
                    end) as tallyStartFlag
            from t
           ) t
     ) t;

处理周末的日子有点棘手。

编辑：

我错过了关于失败的部分。这很容易合并：

select t.*, sum(case when won_flag then tally else 0 end) over (partition by grp order by date) as cumeTally
from (select t.*, sum(tallyStartFlag) over (order by date) as grp
      from (select t.*,
                   (case when won_flag = false
                         then 0
                         when lag(date) over (order by date)) = datedd(day, -1, date) and
                              datename(weekday, date) in ('Tuesday', 'Wednesday', 'Thursday', 'Friday')
                         then 0
                         when lag(date) over (order by date) = dateadd(day, -3, date) and
                              datename(weekday, date) = 'Monday'
                         then 0
                         else 1
                    end) as tallyStartFlag
            from t
           ) t
     ) t;