T-SQL: Get the list of hours between two datetime columns-CodePudding

RAW DATA

table name: guest

guest_id	guest_name	arrival	departure
1	John	2022-01-31 13:00:15	2022-01-31 17:00:12
2	Mary	2022-02-01 12:09:03	2022-02-01 14:00:03

EXPECTED RESULTS

guest_id	guest_name	time
1	John	2022-01-31 13:00:00
1	John	2022-01-31 14:00:00
1	John	2022-01-31 15:00:00
1	John	2022-01-31 16:00:00
1	John	2022-01-31 17:00:00
2	Mary	2022-02-01 12:00:00
2	Mary	2022-02-01 13:00:00
2	Mary	2022-02-01 14:00:00

This is my base query


select guest_id, guest_name, arrival, departure from guest
where guest_name in ('John', 'Mary')

Recursive CTE is fine but not preferred.

CodePudding user response：

select 
    t1.begin_date, 
    t1.end_date, 
    DATEDIFF(hour, begin_date, end_date) as diff_dates 
from (
    select
        convert(datetime, '2022-01-31 13:00:15') as begin_date,
        convert(datetime, '2022-01-31 17:00:12') as end_date
) t1

CodePudding user response：

You need to truncate the dates to nearest hour. Then you can use recursive cte or a join with table of numbers. Recursive cte example:

With rcte as (
    select
        guest_id,
        guest_name,
        time = datetime2fromparts(datepart(year, arrival), datepart(month, arrival), datepart(day, arrival), datepart(hour, arrival), 0, 0, 0, 0),
        dept = datetime2fromparts(datepart(year, departure), datepart(month, departure), datepart(day, departure), datepart(hour, departure), 0, 0, 0, 0)
    from t

    union all

    select 
        guest_id,
        guest_name, 
        dateadd(hour, 1, time),
        dept
    from rcte
    where dateadd(hour, 1, time) <= dept
)
select *
from rcte