none
Need some urgen help with SQL query RRS feed

  • Question

  • Hi

    I'm a little stuck with an SQL qurey I need to write. Can you guys give some guidance?

    Here's the requirement:

    You have a stays table with the below structure (with examples of 2 stays):
    stay_id    event_type     date
    1563071 check-in         2019-03-21
    1563071 check-out       2019-03-23
    1554074 check-in         2019-03-23
    1554074 check-out       2019-03-24

    - Each stay has exactly 2 rows: 1 for check-in and 1 for check-out.
    - All the data in the table is correct and complete (stay_id is unique, no missing rows, check-out
    date is always after the check-in date).

    Write an SQL query to retrieve the average length of stay (number of nights between the
    check-in and check-out).

    Tuesday, December 10, 2019 10:59 PM

All replies

  • Something a little like...

    select 
    	checkins.stay_id, 
    	checkins.date as [checkin date], 
    	checkouts.date as [checkout date], 
    	DATEDIFF(day, checkins.date, checkouts.date) as [days]
    from 
    	stays checkins 
    join 
    	stays checkouts on 
    		checkouts.event_type='check-out' 
    		and checkouts.stay_id=checkins.stay_id
    where 
    	checkins.event_type='check-in'


    I'd rather live with false hope than with false despair.

    Wednesday, December 11, 2019 12:12 AM
  • Hi Archer72,

     

    Would you please try to use the following code:

     

    select

    a.stay_id as stay_id,

    a.date as check_indate,

    b.date as check_outdate,

    DATEDIFF(day, a.date, b.date) as day

    from stays a join stays b on(a.stay_id = b.stay_id and a.event_type <>b.event_type )

    where a. event_type = 'check-in';

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, December 11, 2019 3:12 AM
  • create table #t (stay_id int, event_type varchar(20),dt date)

    insert into #t values (1563071,'check-in','2019-03-21')
    insert into #t values (1563071,'check-out','2019-03-23')
    insert into #t values (1554074,'check-in','2019-03-23')
    insert into #t values (1554074,'check-out','2019-03-24')

    select * from (
    select stay_id,datediff(d,dt,lead(dt) over (partition by stay_id order by dt)) avgdays,
    dt 'check-in',lead(dt) over (partition by stay_id order by dt) check_out from #t
    ) as der where avgdays is not null

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, December 11, 2019 5:14 AM
    Moderator
  • This seems to work too:

    ;

    with Q as

    (

        select stay_id, DATEDIFF(d, MIN([date]), MAX([date])) as nights

        from Stays

        group by stay_id

    )

    select AVG(cast(nights as float)) as [Average length of stay]

    from Q

     


    • Edited by Viorel_MVP Wednesday, December 11, 2019 7:31 AM
    Wednesday, December 11, 2019 7:18 AM