Active status amount as 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 

October 5, student2 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 

October 8, student2 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 October 9, I created another student:

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

October 10th I have this data in the tables.

  select * from #student select * from #student_status_history 

Using the above tables

I have to generate a report on October 10 from October 10 to October 10 of active students as of this day

The output should be lower

  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 a list of dates. I used the Recursive CTE to create dates between a 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 ║ ╚═════════════════════════╩═════════════╝ 

Answer 2

I would do something like this:

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