Статус отслеживания меняется с течением времени
Я пытаюсь отслеживать изменения статуса за определенный диапазон дат. 01.01.2023 - 31.01.2023
Таблица 1: Клиент
Таблица 2: История статуса
Результаты, которые я ищу:
Это один из многих, многих запросов, которые я пробовал:
WITH StatusChanges AS (
SELECT filekey, status, effdate,
ROW_NUMBER() OVER (PARTITION BY filekey ORDER BY effdate) AS rn
FROM empactstatushist
WHERE filekey = 1000 AND effdate <= '2023-01-31'
),
Dates AS (
SELECT DATEADD(day, n, '2023-01-01') AS DateValue
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) - 1 AS n
FROM sys.objects a
) AS n
WHERE DATEADD(day, n, '2023-01-01') < '2023-02-01'
)
SELECT
d.DateValue,
filekey,
sc.status
FROM Dates d
LEFT JOIN StatusChanges sc ON sc.filekey = 1000
AND sc.effdate = (
SELECT MAX(effdate)
FROM StatusChanges sc2
WHERE sc2.filekey = 1000 AND sc2.effdate <= d.DateValue
);
Но это не дает тех результатов, которые мне нужны. Как я могу это сделать?
Похоже, вы хотите создать представление календаря для изменений статуса и хранения окончательного статуса в какой-то другой таблице, что делалось миллионы раз. Календарь или что-то подобное, и вы должны быть в бизнесе. Кстати, формат ММ-дд-гггг самый худший, не используйте его в серьезной работе.
Действительно ли мы всегда ищем только один файловый ключ?
нет, ищу их всех. это был просто пример
Ответы 1
Попробуйте это:
DECLARE @filekey int = 1000;
WITH StatusChanges AS (
SELECT filekey, status, activestatuseffdate effdate
FROM Customer,
WHERE filekey = @filekey AND activestatuseffdate < '20230201'
UNION
SELECT filekey, status, effdate
FROM StatusHistory
WHERE filekey = @filekey AND effdate < '20230201'
),
Dates AS (
SELECT DATEADD(day, n, '20230101') AS DateValue
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) - 1 AS n
FROM sys.objects a
) AS n
WHERE DATEADD(day, n, '20230101') < '20230201'
)
SELECT DateValue, @filekey,
(
SELECT TOP 1 status
FROM StatusChanges
WHERE Filekey = @filekey AND effdate <= DateValue
ORDER BY effdate DESC
) effdate
FROM Dates
ORDER BY DateValue
И для всех файлов клиентов:
WITH StatusChanges AS (
SELECT filekey, status, activestatuseffdate effdate
FROM Customer,
WHERE activestatuseffdate < '20230201'
UNION
SELECT filekey, status, effdate
FROM StatusHistory
WHERE effdate < '20230201'
),
Dates AS (
SELECT DATEADD(day, n, '20230101') AS DateValue
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) - 1 AS n
FROM sys.objects a
) AS n
WHERE DATEADD(day, n, '20230101') < '20230201'
),
DateKeys AS (
SELECT DateValue, filekey
FROM Dates
CROSS JOIN (SELECT DISTINCT filekey FROM Customer) t
)
SELECT dk.DateValue, dk.filekey,
(
SELECT TOP 1 sc.status
FROM StatusChanges sc
WHERE sc.Filekey = dk.filekey AND sc.effdate <= dk.DateValue
ORDER BY sc.effdate DESC
) effdate
FROM DateKeys dk
Вы захотите добавить к этому ORDER BY, но неясно, хотите ли вы сначала filekey или DateValue.
это работает для filekey=1000, как мне изменить его, чтобы получить тот же тип данных, но для всех соответствующих файловых ключей
@Green Это в ответе.
столбец filekey недействителен в 2 x dk.filekey, и ключ файла из дат также недействителен
@Green Отсутствовал псевдоним таблицы для подзапроса перекрестного соединения.
Вот и все! Спасибо!
Так в чем твой вопрос..? Вы рассказали нам, что пытаетесь сделать, но не рассказали, о чем просите. А что насчет того, что вы пробовали, не работает? Что ты пробовал? А как насчет контента, который вы прочитали, когда застряли, разве вы не поняли?