Блог

У меня есть эти данные:

 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 
  

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 
  

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

9 октября я создал другого ученика:

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

10 октября у меня есть эти данные в таблицах.

     select * from #student 
    select * from #student_status_history
  

Используя приведенные выше таблицы

Я должен сгенерировать отчет 10 октября с 1 по 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
  

Вот один из способов сделать это

Вам нужна calendar таблица со списком дат. Я использовал 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 ║
╚═════════════════════════╩═════════════╝
  

Я бы сделал что-то вроде этого:

 ;with cte_dates as (
    select convert(date, '20161001', 112) as [date]
    union all
    select dateadd(day, 1, [date]) as [date]
    from cte_dates as d
    where
        d.[date] < convert(date, '20161010', 112)
)
select
    d.[date] as [Date],
    sum(case when isnull(sh.to_status, s.[status]) = 'Active' then 1 else 0 end) as ActiveCount
from cte_dates as d
    left join #student as s on
        s.created_dt <= d.[date]
    outer apply (
        select top 1 sh.to_status
        from #student_status_history as sh
        where
            sh.student_id = s.student_id and
            sh.status_changed_dt <= d.[date]
        order by
            sh.status_changed_dt desc
    ) as sh
group by
    d.[date]