Post reply

Relatively easy query assistance - datediff

  • January 18, 2020 at 3:19 pm

    #3716530

    Hi all,

    I have a question since I'm new to SQL:

    I have a table which contains 3 columns: a customer_id, type_of_event which is check-in or check-out, and date.

    so each customer_id has exactly 2 rows of check in and check out.

    I need to calculate the general AVERAGE amount of days of stay (average of all stays in term of days)

    I hope I'm clear enough. Would appreciate some help.

    Thanks a lot!

  • January 18, 2020 at 3:56 pm

    #3716535 

    with
    hotel_cte(customer_id, type_of_event, event_dt) as (
    select 1, 'check in', '2019-12-30'
    union all
    select 1, 'check out', '2020-01-14'
    union all
    select 2, 'check in', '2020-01-14'
    union all
    select 2, 'check out', '2020-01-22'
    union all
    select 3, 'check in', '2020-01-11'
    union all
    select 3, 'check out', '2020-01-18'
    union all
    select 4, 'check in', '2020-01-01'
    union all
    select 4, 'check out', '2020-01-02'),
    cte(customer_id, hotel_days) as (
    select 
      cin.customer_id, datediff(day, cin.event_dt, cout.event_dt)
    from
      hotel_cte cin
     join
      hotel_cte cout on cin.customer_id=cout.customer_id
    and cin.type_of_event='check in'
    and cout.type_of_event='check out')
    select avg(hotel_days+0.0) avg_days
    from cte;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply