So, I want to get the average days of how long user stay in the hotel. I tried using group by and bunch stuff for like an hour and can't seem to get the result that I want. The table looks like this.

The result table should look like this

I'm pretty new to SQL so I don't know much things, please help me lol, thanks!

CodePudding user response：

WITH CTE(Name,  Stay_dt,    Status) AS
(
  SELECT'Paman',    '2021-01-13',   'In' UNION ALL
  SELECT'Paman',    '2021-01-15' ,  'Out'UNION ALL
  SELECT'Naman',    '2021-01-17',   'In'UNION ALL
  SELECT'Naman',    '2021-01-19',   'Out'UNION ALL
  SELECT'Paman',    '2021-01-22',   'In'UNION ALL
  SELECT'Paman',    '2021-01-25',   'Out'UNION ALL
  SELECT'Paman',    '2021-01-27',   'In'
)
SELECT SQ.Name,AVG(ABS(DATEDIFF(HOUR,SQ.STAY_DT,SQ.X_OUT)))/24.0 AVG_DAYS FROM
(
  SELECT C.NAME,CAST(C.STAY_DT AS DATE)STAY_DT,C.STATUS,X.X_OUT
  FROM CTE AS C
  CROSS APPLY
  (
    SELECT TOP 1 CAST(X.STAY_DT AS DATE)X_OUT
    FROM CTE AS X
    WHERE C.Name=X.Name AND X.Status='Out' AND CAST(X.STAY_DT AS DATE)>CAST(C.STAY_DT AS DATE) 
    ORDER BY X_OUT ASC 
  )X
  WHERE C.Status='In'
)SQ GROUP BY SQ.Name

As you haven't specified RDBMS you use this query is for Microsoft SQL Server. CTE is a representation of your table's data

CodePudding user response：

Assuming no breaks in the sequence:

with data as (
    select *, lead(Stay_dt) over (partition by Name order by Stay_dt) as Next_dt
    from T
)
select Name, avg(datediff(day, Stay_dt, Next_dt)) as Avg_days
from data
where Status = 'In'
group by Name;