J*y
发帖数: 271 | 1 有没有MS SQL server编程的大牛？
my data is about residents in a room. residents entered and left the room at
different dates. I need to count the # of residents each day. the actual
code looks like this:
select ID, DT from Table where StartDate<=DT+1 and EndDate>=DT
union all
select ID, DT+1 from Table where StartDate<=DT+2 and EndDate>=DT+1
union all
select ID, DT+2 from Table where StartDate<=DT+3 and EndDate>=DT+2
...
til DT+n=getdate()
能把这个程序用循环或者recursive table 简化一下？ | z*****2
发帖数: 56 | 2 Try it with recursive CTE. | f*****3
发帖数: 19 | 3 这个不需要Recursive CTE.
第一步：
CTE or temp table
create table date_lookup as (
date date,
day_start datetime，
day_end datetime
);
populate this table with the pre-defined date range you need to look into.
第二部：
select
dl.date, count(*)
from date_lookup as dl
inner join hotel_record as hr
on hr.start_date < dl.day_end
and hr.end_date >= dl.day_start
group by dl.date; | R**********6
发帖数: 4 | 4 -- testing data
create table #hotel_guest
(
customerID int
, checkinDate date
, checkOutDate date
)
insert into #hotel_guest
values(1, '1/13/2022', '1/25/2022')
, (2, '1/6/2022', '1/10/2022')
, (3, '1/8/2022', '1/29/2022')
, (4, '1/17/2022', '1/19/2022')
, (5, '1/21/2022', '1/23/2022')
, (2, '1/15/2022', '1/18/2022')
-- build a calendar based on the date range
create table #calendar
(
calendar_date date
)
declare @start date, @end date, @loop date
select @start = min(checkinDate), @end = max(checkoutDate)
from #hotel_guest
set @loop = @start
while (@loop <= @end)
begin
insert into #calendar(calendar_date)
values(@loop)
set @loop = dateadd(d, 1, @loop)
end
select cal.calendar_date, COUNT(*)
-- select *
from #calendar cal
join #hotel_guest hg on hg.checkinDate <= cal.calendar_date
and hg.checkOutDate >=cal.calendar_date -- adjust accordingly if checkout
date does not count
group by cal.calendar_date
order by 1 | f*****3
发帖数: 19 | 5 This query will miss those checked in during the date been computed.
It is a little anti-intuition but actually the condition should be:
CheckInTime <= End of Day AND
CheckOutTime > Begin of Day
As a day is a DURATION, not a moment.
【在 R**********6 的大作中提到】
: -- testing data
: create table #hotel_guest
: (
: customerID int
: , checkinDate date
: , checkOutDate date
: )
: insert into #hotel_guest
: values(1, '1/13/2022', '1/25/2022')
: , (2, '1/6/2022', '1/10/2022')
| f*****3
发帖数: 19 | 6 This query will miss those checked in during the date been computed.
It is a little anti-intuition but actually the condition should be:
CheckInTime <= End of Day AND
CheckOutTime > Begin of Day
As a day is a DURATION, not a moment.
【在 R**********6 的大作中提到】
: -- testing data
: create table #hotel_guest
: (
: customerID int
: , checkinDate date
: , checkOutDate date
: )
: insert into #hotel_guest
: values(1, '1/13/2022', '1/25/2022')
: , (2, '1/6/2022', '1/10/2022')
| R**********6
发帖数: 4 | 7 to be frank, I don't think your query is correct. or, could you post your
entire script?
【在 f*****3 的大作中提到】
: This query will miss those checked in during the date been computed.
: It is a little anti-intuition but actually the condition should be:
: CheckInTime <= End of Day AND
: CheckOutTime > Begin of Day
: As a day is a DURATION, not a moment.
| c*****m
发帖数: 1160 | 8 换用一个程序，比如python:
firstdate = cur.execute('select min(checkindate) from guest')
lastdate = cur.execute('select max(checkoutdate) from guest')
for date in range(firstdate, lastdate):
guest_num = cur.execute('select count(*) from guest where checkindate
and checkoutdate>date ')
#exporte (date, guest_num) |
|
