从SQL Server中的几个日期范围填充日期列表 [英] Populating a list of dates from a few date ranges in SQL server

问题描述

我有一长串列出日期范围和成本的帐户，例如以下两行：

I have a long list of accounts with date ranges and costs, here are two rows for example:

Account    Start_date   End_date    Cost
Megadeal    11/6/2015   13/6/2015   1000$ 
Superstar   12/8/2014   14/8/2014   2000$ 

我需要填充一个列表，其中包含每个范围内的所有日期，每一天每一行，以及每一天的帐户名称和费用。结果应如下所示：

And I need to populate a list with all the dates in each range, one row for each day, and each day with the name of the account and cost. The result should look something like that:

Megadeal    11/6/2015   1000
Megadeal    12/6/2015   1000
Megadeal    13/6/2015   1000
Superstar   12/8/2014   2000
Superstar   13/8/2014   2000
Superstar   14/8/2014   2000

意思是每次填充不同范围（不同的开始日期和结束日期）中的日期列表。

Meaning to populate the list of dates from a different range every time (different start date and end date).

有什么建议吗？

推荐答案

您可以使用 Tally表 以生成日期：

You can use a Tally Table to generate the dates:

DECLARE @maxDiff INT;

SELECT @maxDiff = MAX(DATEDIFF(DAY, Start_Date, End_Date)) FROM tbl;

WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
    SELECT TOP(@maxDiff + 1) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E8
)
SELECT
    t.Account,
    Date = DATEADD(DAY, ct.N - 1, t.Start_Date),
    t.Cost
FROM tbl t
CROSS JOIN CteTally ct
WHERE DATEADD(DAY, ct.N - 1, t.Start_Date) <= t.End_Date
ORDER BY t.Account, Date;

这篇关于从SQL Server中的几个日期范围填充日期列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！