Help with a query - time difference between multiple rows

1

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!

2

hi hope this helps :slight_smile:

please click arrow to the left for Drop Create Sample Data 
drop table #sampledata
go 


create table #sampledata
(
customer_id int ,
type_of_event varchar(10),
event_date date 
)
go 

insert into #sampledata select 134,'CHECK-IN','2019-08-10'
insert into #sampledata select 134,'CHECK-OUT','2019-08-12'

insert into #sampledata select 267,'CHECK-IN','2019-07-15'
insert into #sampledata select 267,'CHECK-OUT','2019-08-25'
go 

select * from #sampledata
go

image
image723×210 6.27 KB 

select 
  avg(datediff(dd,a.event_date,b.event_date))  as avg_days_stayed 
from 
	(select * from #sampledata where type_of_event = 'CHECK-IN' )  a 
		join 
	(select * from #sampledata where type_of_event = 'CHECK-OUT' )  b	
		on 
		 a.customer_id = b.customer_id

image

1 Like
3

Thanks so much!

On what version of SQL did you create this? I use pgAdmin and it seems to not follow through with this code

4

hi

i used microsoft SQL Server 2012 ...

avg might be ok
but datediff .. is SQL Server !!!

Rest is straight forward joins

5

[quote="harishgg1, post:2, topic:17146"]
select avg(datediff(dd,a.event_date,b.event_date)) as avg_days_stayed from (select * from #sampledata where type_of_event = 'CHECK-IN' ) a join (select * from #sampledata where type_of_event = 'CHECK-OUT' ) b on a.customer_id = b.customer_id
[/quote

Oh I'm using Mac... goddamn apple.