sql-server - 事件状态总和，如 SQL Server 2012 中的 scd

Question

我有这些数据:

CREATE TABLE #student
(
     student_id INT,
     status     VARCHAR(50),
     created_dt DATE
)

CREATE TABLE #student_status_history
(
     student_id        INT,
     from_status       VARCHAR(50),
     to_status         VARCHAR(50),
     status_changed_dt DATE
)

INSERT INTO #student (student_id, status, created_dt)
VALUES (1, 'Active', '2016-10-02'),
       (2, 'Active', '2016-10-02'),
       (3, 'Active', '2016-10-02')

SELECT *
FROM #student 

10 月 5 日，student2 状态更新为 inactive

UPDATE #student
SET status = 'Inactive'
WHERE student_id = 2

INSERT INTO #student_status_history (student_id, from_status, to_status, status_changed_dt)
VALUES (2, 'Active', 'Inactive', '2016-10-05')

SELECT *
FROM #student

SELECT *
FROM #student_status_history 

10 月 8 日，student2 状态更新为 active:

UPDATE #student
SET status = 'Active'
WHERE student_id = 2

INSERT INTO #student_status_history (student_id, from_status, to_status, status_changed_dt)
VALUES (2, 'InActive', 'Active', '2016-10-08') 

10 月 9 日，我创建了另一个学生:

INSERT INTO #student (student_id, status, created_dt)
VALUES (4, 'Active', '2016-10-09') 

10 月 10 日，我在表格中保存了这些数据。

    select * from #student 
    select * from #student_status_history

使用上表

我应该在 10 月 10 日生成一份报告，涵盖 10 月 1 日到 10 月 10 日当天的活跃学生

输出应如下所示

  Date                  ActiveCount 
  -----------           -----------
  2016-10-01                 0
  2016-10-02                 3
  2016-10-04                 3
  2016-10-05                 2
  2016-10-06                 2
  2016-10-07                 2
  2016-10-08                 3
  2016-10-09                 4
  2016-10-10                 4

Answer 1

这是一种方法

您需要一个包含日期列表的日历表。我使用Recursive CTE来生成日期范围之间的日期。

;WITH calendar
     AS (SELECT dates = CONVERT(DATETIME, '2016-10-01')
         UNION ALL
         SELECT dates = Dateadd(DAY, 1, dates)
         FROM   calendar
         WHERE  dates < '2016-10-10')
SELECT c.dates,
       Count(s.created_dt) AS ActiveCount
FROM   calendar c
       LEFT JOIN #student s
              ON s.created_dt <= c.dates
WHERE  NOT EXISTS (SELECT 1
                   FROM   #student_status_history sh
                   WHERE  sh.student_id = s.student_id
                   HAVING c.dates BETWEEN Min(CASE
                                                WHEN from_status = 'active '
                                                     AND to_status = 'Inactive' THEN status_changed_dt
                                              END) AND Max(CASE
                                                             WHEN to_status = 'active '
                                                                  AND from_status = 'Inactive' THEN Dateadd(dd, -1, status_changed_dt)
                                                           END))
GROUP  BY c.dates
OPTION (MAXRECURSION 0) 

结果:

╔═════════════════════════╦═════════════╗
║          dates          ║ ActiveCount ║
╠═════════════════════════╬═════════════╣
║ 2016-10-01 00:00:00.000 ║           0 ║
║ 2016-10-02 00:00:00.000 ║           3 ║
║ 2016-10-03 00:00:00.000 ║           3 ║
║ 2016-10-04 00:00:00.000 ║           3 ║
║ 2016-10-05 00:00:00.000 ║           2 ║
║ 2016-10-06 00:00:00.000 ║           2 ║
║ 2016-10-07 00:00:00.000 ║           2 ║
║ 2016-10-08 00:00:00.000 ║           3 ║
║ 2016-10-09 00:00:00.000 ║           4 ║
║ 2016-10-10 00:00:00.000 ║           4 ║
╚═════════════════════════╩═════════════╝