假設我有一個日期范圍，@StartDate = 2022-01-01 和 @EndDate = 2022-02-01，這是一個報告期。
此外，我還有客戶記錄，其中每個客戶都有一個 LIVE Date 和一個 ServiceEndDate（或 ServiceEndDate = NULL，因為他們是一個持續的客戶）
某些客戶的實時日期和服務結束日期范圍可能超出報告期范圍。我只想報告他們在此期間是客戶的幾天。
| 名稱 | 直播日期 | 服務結束日期 |
|---|---|---|
| 湯姆 | 2021-10-11 | 2022-01-13 |
| 標記 | 2022-11-13 | 2022-02-15 |
| 安迪 | 2022-01-02 | 2022-02-10 |
| 搶 | 2022-01-09 | 2022-01-14 |
我想創建一個表，其中 A 列是日期（在報告期間的每個日期之間迭代），B 列是該日期作為客戶的客戶數量的總和。
像這樣的東西
| 日期 | 客戶數量 |
|---|---|
| 2022-01-01 | 2 |
| 2022-01-02 | 3 |
| 2022-01-03 | 3 |
| 2022-01-04 | 3 |
| 2022-01-05 | 3 |
| 2022-01-06 | 3 |
| 2022-01-07 | 3 |
| 2022-01-08 | 3 |
| 2022-01-09 | 4 |
| 2022-01-10 | 4 |
| 2022-01-11 | 4 |
| 2022-01-12 | 4 |
| 2022-01-13 | 4 |
| 2022-01-14 | 3 |
| 2022-01-15 | 3 |
依此類推，直到@EndDate 結束
任何幫助將不勝感激，謝謝。
uj5u.com熱心網友回復：
您可以將您的表格加入到包含您需要的所有日期的日歷表格中：
with calendar as
(select cast('2022-01-01' as datetime) as d
union all select dateadd(day, 1, d)
from calendar
where d < '2022-02-01')
select d as "Date", count(*) as NumberOfCustomers
from calendar inner join table_name
on d between LiveDate and coalesce(ServiceEndDate, '9999-12-31')
group by d;
小提琴
uj5u.com熱心網友回復：
我個人建議使用 Tally，而不是 rCTE，因為 Tally 的性能要好得多。
SELECT *
INTO dbo.YourTable
FROM (VALUES('Tom ',CONVERT(date,'2021-10-11 '),CONVERT(date,'2022-01-13')),
('Mark',CONVERT(date,' 2022-11-13'),CONVERT(date,' 2022-02-15')),
('Andy',CONVERT(date,' 2022-01-02'),CONVERT(date,' 2022-02-10')),
('Rob ',CONVERT(date,'2022-01-09 '),CONVERT(date,'2022-01-14')))V(Name,LiveDate,ServiceEndDate);
GO
SELECT *
FROM dbo.YourTable;
GO
DECLARE @StartDate date = '20220101',
@EndDate date = '20220201';
WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT 0 AS I
UNION ALL
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
FROM N N1, N N2, N N3), --up to 1,000 days
Dates AS(
SELECT DATEADD(DAY, T.I, @StartDate) AS Date
FROM Tally T)
SELECT D.Date,
COUNT(YT.[Name]) AS NumberOfCustomers
FROM Dates D
LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.LiveDate
AND (D.[Date] <= YT.ServiceEndDate
OR YT.ServiceEndDate IS NULL)
GROUP BY D.[Date]
ORDER BY D.[Date];
GO
DROP TABLE dbo.YourTable;
請注意，結果并不能反映您的預期結果，我懷疑您的預期結果是錯誤的。例如，您有 2 個人住在 2022-01-01，但是，只有 1 個人住在那個日期：湯姆。
該解決方案也永遠不會將標記為“活動”（另一個答案中的 rCTE 方法也不會），因為它們的結束日期早于它們的活動日期。如果有人可以在服務開始之前結束服務，我建議您有資料質量問題，您應該CHECK CONSTRAINT在表中添加 a 以確保 is 的ServiceEndDate值>= LiveDate。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/425862.html
上一篇：當滿足特定條件時，通過向字串添加/洗掉字符來清理資料T-SQL
下一篇：自聯接以更新SQL中的表