时间戳

首页 > 解决方案 > 如何对一组记录进行分组（即进入发票/计费周期）

sql - 如何对一组记录进行分组（即进入发票/计费周期）

问题描述

对于按项目划分的一组发票阶段记录，我正在尝试使用起始发票阶段的 ID 来确定计费周期。

这是桌子——InvoiceStages

|ID| Project | StageDate | InvoiceStage            | StageFlag | BillCycle |
|1 | abc123  | 10-May-18 | Finance                 |     S     |    1      |
|2 | abc123  | 15-May-18 | Review Draft            |           |    1      |
|4 | abc123  | 19-May-18 | Approved - NO Changes   |           |    1      |
|7 | abc123  | 21-May-18 | Final Invoice           |     E     |    1      |
|9 | abc123  | 05-Jun-18 | Finance                 |     S     |    9      |
|12| abc123  | 07-Jun-18 | Review Draft            |           |    9      |
|15| abc123  | 09-Jun-18 | Approved - With Changes |           |    9      |
|21| abc123  | 10-Jun-18 | Review Draft            |           |    9      |
|25| abc123  | 12-Jun-18 | Approved - NO Changes   |           |    9      |
|40| abc123  | 13-Jun-18 | Final Invoice           |     E     |    9      |
|3 | xyz789  | 15-May-18 | Finance                 |     S     |    3      |
|5 | xyz789  | 19-May-18 | Review Draft            |           |    3      |
|6 | xyz789  | 20-May-18 | Approved - NO Changes   |           |    3      |
|8 | xyz789  | 22-May-18 | Final Invoice           |     E     |    3      |
|10| xyz789  | 06-Jun-18 | Finance                 |     S     |    10     |
|11| xyz789  | 07-Jun-18 | Review Draft            |           |    10     |
|18| xyz789  | 09-Jun-18 | Approved - NO Changes   |           |    10     |
|22| xyz789  | 11-Jun-18 | Final Invoice           |     E     |    10     |

我查看了 LAG / LEAD，但不确定这是否是最佳选择。

Select
    ID
    , Project
    , StageDate
    , InvoiceStage
    , StageFlag
    , ?? As BillCycle
From InvoiceStages

我希望 BillCycle 的输出是第一条记录的 ID，其中 StageFlag = 'S' 的所有记录直到并包括结束阶段'E'。然后下一组将再次以“S”开头的 ID 开始。

标签： sqltsqlgroup-bygroupingsql-server-2016

解决方案

您可以使用累积和分配一个组，然后使用窗口函数来获取值：

select i.*,
       max(id) over (partition by project, grp) as invoice_id
from (select i.*,
             sum(case when i.stageflag = 'S' then 1 else 0 end) over (partition by i.project order by i.stagedate) as grp
      from InvoiceStages i
     ) i;

如果 id 随日期增加，您可以在没有子查询的情况下执行此操作：

select i.*,
       max(case when stageflag = 'S' then id end) over (partition by project) as invoice_id
from invoicestages i;

推荐阅读