#sql-server #tsql #sql-server-2012
#sql-сервер #tsql #sql-server-2012
Вопрос:
У меня есть эти данные:
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
Комментарии:
1. какова логика для
2016-10-04 3
Ответ №1:
Вот один из способов сделать это
Вам нужна 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 ║
╚═════════════════════════╩═════════════╝
Комментарии:
1. Отличное решение!
2. хорошее решение, однако, если ученик 2 снова неактивен 11-го числа и остается неактивным, решение не показывает правильное количество активных для любой даты после 11-го. более простой
NOT EXISTS
проверкой было бы проверить максимальную измененную дату, преждеdates
чем сделать запись неактивной, напримерNOT EXISTS (SELECT 1 FROM #student_status_history sh WHERE sh.student_id = s.student_id AND c.dates >= status_changed_dt HAVING MAX(CASE WHEN from_status = 'active ' AND to_status = 'Inactive' THEN status_changed_dt END) = MAX(status_changed_dt))
Ответ №2:
Я бы сделал что-то вроде этого:
;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]