Как создавать новые строки для каждого месяца в пределах диапазона дат?

#sql-server #date #calendar #range #record

#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. Любая помощь была бы высоко оценена.

1. Я бы рекомендовал использовать хранимую процедуру вместо view, поскольку вы не можете использовать insert в представлении, а хранимая процедура обеспечит вам гораздо лучший контроль

Ответ №1:

Вот подход, основанный на подсчете или таблице чисел. Tally CTE создает последовательность (до 12 ^ 4 или 20 736 дней (которая может быть увеличена до любой необходимой)), чтобы расширить календарь на дни. Затем он агрегируется по месяцам.

 ;with
n(n) as (select * from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) v(n)),
tally_cte(N) as (
     select row_number() over (order by (select null)) 
     from n n1 cross join n n2 cross join n n3 cross join n n4)
select booking_id, booking_name, start_dt, end_dt, dfp.dfp,
       year(dt.booking_dt) [Year], convert(char(3), dt.booking_dt, 0) [Month],
       count(*) booked_days
from #test t
     cross apply
     (select dateadd(d, n-1, t.start_dt) booking_dt 
      from tally_cte
      where n<=datediff(d, t.start_dt, t.end_dt)) dt
     cross apply
     (select datefromparts(year(dt.booking_dt), month(dt.booking_dt), 1) dfp) dfp
group by booking_id, booking_name, start_dt, end_dt, dfp.dfp,
       year(dt.booking_dt), convert(char(3), dt.booking_dt, 0)
order by 1, 3, 5;

Вывод

 booking_id          booking_name    start_dt    end_dt  dfp Year    Month   booked_days
1   HolidayUSA      2020-01-01      2020-02-15  2020-01-01  2020    Jan     31
1   HolidayUSA      2020-01-01      2020-02-15  2020-02-01  2020    Feb     14
2   HolidayEurope   2020-01-20      2020-03-10  2020-01-01  2020    Jan     12
2   HolidayEurope   2020-01-20      2020-03-10  2020-02-01  2020    Feb     29
2   HolidayEurope   2020-01-20      2020-03-10  2020-03-01  2020    Mar     9
3   HolidayUK       2020-03-14      2020-03-19  2020-03-01  2020    Mar     5

Ответ №2:

Это мое самое близкое приближение:

 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]))