时间戳

首页 > 解决方案 > 如何在日期范围内为每个月创建新行？

sql-server - 如何在日期范围内为每个月创建新行？

问题描述

我需要创建一个视图，为日期范围之间的每个月创建新行。我的预订表的数据示例如下所示。

Booking ID  Booking Name   Start Date  End Date    Booked Days
1           HolidayUSA     1/01/2020   15/02/2020  46
2           HolidayEurope  20/01/2020  10/03/2020  50
3           HolidayUK      14/03/2020  19/03/2020  5

我想创建一个视图，该视图将为日期范围之间的每个月添加一条新记录，根据该月的天数计算预订天数，并添加月/年字段。

预期输出：

预期输出表

我正在使用 Microsoft SQL Server。任何帮助将不胜感激。

标签： sql-serverdatecalendarrangerecord

解决方案

这是我最接近的近似值：

declare @tb table  ([Booking Id] int, [Booking Name] varchar(30),
                    [Start Date] datetime, [End Date] datetime)
-- Sample data
insert @tb values
    (1, 'HolidayUSA','20200101','20200215'),
    (2, 'HolidayEUROPE','20200120','20200310'),
    (3, 'HolidayUK','20200314','20200319')

-- Showing sample data
select *, dateDiff(dd, [Start Date], [End Date]) [Booked Days]
from @tb

declare @dd smallint
select @dd = max(dateDiff(dd, [Start Date], [End Date])) from @tb

with day_cte as (
    select 1 as nbd union all
    select nbd + 1 from day_cte where nbd <= @dd)
select [Booking Id], [Booking Name], [Start Date], [End Date]
    , datePart(yy, dateAdd(dd, nbd, [Start Date])) [Year]
    , left(dateName(mm, dateAdd(dd, nbd, [Start Date])), 3) [Month]
    , count(*) [Booked Days]
from @tb, day_cte
where nbd <= dateDiff(dd, [Start Date], [End Date])
group by [Booking Id], [Booking Name], [Start Date], [End Date]
    , datePart(yy, dateAdd(dd, nbd, [Start Date]))
    , dateName(mm, dateAdd(dd, nbd, [Start Date]))

推荐阅读