Active status sum like scd in SQL Server 2012

Question

I have this data:

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 

On 5th october student2 status is updated to 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 

On 8th october student2 status is updated to 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') 

On 9th Oct I created another student:

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

On 10th october I have this data in tables.

    select * from #student 
    select * from #student_status_history

Using the above tables

I should generate a report on 10th october as from Oct 1st to Oct 10th active students as of on that day

Output should be as below

  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

Here is one way to do it

You need a calendar table with list of dates. I have used Recursive CTE to generate the dates between the date range.

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

Result :

╔═════════════════════════╦═════════════╗
║          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 ║
╚═════════════════════════╩═════════════╝