Midnight counts using date ranges

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Midnight counts using date ranges

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-10 : 09:43:55

Hello,

I have a table holding Hospital Ward data with an Admit and Discharge date.
(Sample data shown in #data)

I would like to use #data to calculate the ‘Midnight Bed Occupancy’ for every date (day) available in #data
(I have provided a sample of how the output might appear #dataMidnightOccupancy)

Can anyone help with this query – can it be done using a query?
Thanks

I have used this forum before and had some amazing help – here’s hoping ;-)

--sample SQL
create table
#data
(
LocalID nvarchar (30),
wardID nvarchar (10),
startdate datetime null,
enddate datetime null
)

GO
SET ANSI_PADDING OFF

insert into #data
select '000001', 'ward1', convert(datetime,'19/02/2008 08:35:00',103), convert(datetime,'19-02-2008 12:53:00',103) UNION all
select '000002', 'ward3', convert(datetime,'19/02/2008 14:35:00',103), convert(datetime,'20/02/2008 08:40:00',103) UNION all
select '000002', 'ward1', convert(datetime,'20/02/2008 08:40:00',103), convert(datetime,'21/02/2008 14:40:00',103) UNION all
select '000002', 'ward9', convert(datetime,'21/02/2008 14:40:00',103), convert(datetime,'22/02/2008 16:40:00',103) UNION all
select '000003', 'ward9', convert(datetime,'19/02/2008 08:35:00',103), convert(datetime,'22/02/2008 12:53:00',103)

select * from #data

create table
#dataMidnightOccupancy
(
wardID nvarchar (10),
MidnightDate datetime ,
MidnightCount nvarchar (30)
)

insert into #dataMidnightOccupancy
select 'ward1', convert(datetime,'19/02/2008',103), '0' UNION all
select 'ward1', convert(datetime,'20/02/2008',103), '0' UNION all
select 'ward1', convert(datetime,'21/02/2008',103), '1' UNION all
select 'ward1', convert(datetime,'22/02/2008',103), '0' UNION all
select 'ward3', convert(datetime,'19/02/2008',103), '0' UNION all
select 'ward3', convert(datetime,'20/02/2008',103), '1' UNION all
select 'ward3', convert(datetime,'21/02/2008',103), '0' UNION all
select 'ward3', convert(datetime,'22/02/2008',103), '0' UNION all
select 'ward9', convert(datetime,'19/02/2008',103), '0' UNION all
select 'ward9', convert(datetime,'20/02/2008',103), '1' UNION all
select 'ward9', convert(datetime,'21/02/2008',103), '1' UNION all
select 'ward9', convert(datetime,'22/02/2008',103), '2'

select * from #dataMidnightOccupancy

drop table #data
drop table #dataMidnightOccupancy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 10:33:33

Can you explain how you will arrive at those numbers for Midnight Count?

------------------------------------------------------------------------------------------------------
SQL Server MVP

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-10 : 10:53:25

...Thanks - ahemmmm now i have realised an error in my output data that has probably confused things – hence your question. sorry!
(I have just edited this)
Highlights the problem in doing things manually!

I want to count when a patient is ‘on a ward at the stroke of Midnight’

e.g.
Ward1 would have had a 000001 in bed for a few hours on the 19/02/2008 but discharged before midnight (and not counted)
Ward1 will have had 000002 in a bed on midnight on the 20/02/2008 (and counts as 1)
The rest of the days nobody was in a bed.

hope this helps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 11:10:40

sorry how did 20/02/2008 count became 1? the data for ward1 contains only '20/02/2008 08:40:00' to '21/02/2008 14:40:00' which is after midnight so shouldnt it be for 21 that count is 1

------------------------------------------------------------------------------------------------------
SQL Server MVP

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-10 : 11:25:39

i see what you are saying - it depends on what date you associate with midnight

I have;
'20/02/2008 08:40:00' to '21/02/2008 14:40:00' associated with midnight on 20th.

Looking at what you say the patient would be on the ward at '21/02/2008 00:00:00' which is midnight on the 21st.

I have updated my output data to reflect this.
(im trying not to get embarrassed while sat at my PC!!!)

I really hope we can sort this as it will prove really useful

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-02-10 : 11:32:30

[CODE]select c.MyDate, d.Ward, count(*) MidnightBedOccupancy
from #data d
inner join
Calendar c -- Your generic Calendar table (Go create one if you need to!)
on
c.MyDate between d.startdate and d.enddate
group by
c.MyDate, d.Ward[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-10 : 11:43:13

Thanks Bustaz Kool - i will try & implement your method.

I take it that a generic calendar table is a must for this type of query.

Good incorporation of the Mark Twain quote ;-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 11:48:37

I created one on the fly!


DECLARE @MinDate datetime,@MaxDate datetime

SELECT @MinDate=DATEADD(dd,DATEDIFF(dd,0,MinDate),0),@MaxDate=DATEADD(dd,DATEDIFF(dd,0,MaxDate),0)
FROM (SELECT MIN(startdate) AS MinDate,MAX(enddate) AS MaxDate FROM #data)t




SELECT r.wardID,r.[Date] AS MidnightDate,COUNT(LocalID) AS MidnightCnt
FROM
(
SELECT t.wardID,d.[Date]
FROM (SELECT DISTINCT wardID FROM #data) t
CROSS JOIN(
SELECT DATEADD(dd,DATEDIFF(dd,0,@MinDate) + v.number,0) AS [Date]
FROM master..spt_values v
WHERE v.type='p'
AND DATEADD(dd,DATEDIFF(dd,0,@MinDate) + v.number,0)<=@MaxDate
)d
)r
LEFT JOIN #data d
ON d.wardID=r.wardID
AND r.Date BETWEEN d.startdate AND d.enddate
GROUP BY r.wardID,r.[Date]
ORDER BY r.wardID,r.[Date]


output
---------------------------------------------
wardID     MidnightDate            MidnightCnt
---------- ----------------------- -----------
ward1      2008-02-19 00:00:00.000 0
ward1      2008-02-20 00:00:00.000 0
ward1      2008-02-21 00:00:00.000 1
ward1      2008-02-22 00:00:00.000 0
ward3      2008-02-19 00:00:00.000 0
ward3      2008-02-20 00:00:00.000 1
ward3      2008-02-21 00:00:00.000 0
ward3      2008-02-22 00:00:00.000 0
ward9      2008-02-19 00:00:00.000 0
ward9      2008-02-20 00:00:00.000 1
ward9      2008-02-21 00:00:00.000 1
ward9      2008-02-22 00:00:00.000 2



--your reqd output
wardID     MidnightDate            MidnightCount
---------- ----------------------- ------------------------------
ward1      2008-02-19 00:00:00.000 0
ward1      2008-02-20 00:00:00.000 0
ward1      2008-02-21 00:00:00.000 1
ward1      2008-02-22 00:00:00.000 0
ward3      2008-02-19 00:00:00.000 0
ward3      2008-02-20 00:00:00.000 1
ward3      2008-02-21 00:00:00.000 0
ward3      2008-02-22 00:00:00.000 0
ward9      2008-02-19 00:00:00.000 0
ward9      2008-02-20 00:00:00.000 1
ward9      2008-02-21 00:00:00.000 1
ward9      2008-02-22 00:00:00.000 2

------------------------------------------------------------------------------------------------------
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 11:55:16

or if you dont want to use spt_values use the below solution


DECLARE @MinDate datetime,@MaxDate datetime

SELECT @MinDate=DATEADD(dd,DATEDIFF(dd,0,MinDate),0),@MaxDate=DATEADD(dd,DATEDIFF(dd,0,MaxDate),0)
FROM (SELECT MIN(startdate) AS MinDate,MAX(enddate) AS MaxDate FROM #data)t




SELECT r.wardID,r.[Date] AS MidnightDate,COUNT(LocalID) AS MidnightCnt
FROM
(
SELECT t.wardID,d.[Date]
FROM (SELECT DISTINCT wardID FROM #data) t
CROSS JOIN dbo.CalendarTable(@MinDate,@MaxDate,0) d
)r
LEFT JOIN #data d
ON d.wardID=r.wardID
AND r.Date BETWEEN d.startdate AND d.enddate
GROUP BY r.wardID,r.[Date]
ORDER BY r.wardID,r.[Date]



output
------------------------------------------
wardID     MidnightDate            MidnightCnt
---------- ----------------------- -----------
ward1      2008-02-19 00:00:00.000 0
ward1      2008-02-20 00:00:00.000 0
ward1      2008-02-21 00:00:00.000 1
ward1      2008-02-22 00:00:00.000 0
ward3      2008-02-19 00:00:00.000 0
ward3      2008-02-20 00:00:00.000 1
ward3      2008-02-21 00:00:00.000 0
ward3      2008-02-22 00:00:00.000 0
ward9      2008-02-19 00:00:00.000 0
ward9      2008-02-20 00:00:00.000 1
ward9      2008-02-21 00:00:00.000 1
ward9      2008-02-22 00:00:00.000 2

CalendarTable function can be found here

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP

DLTaylor
Posting Yak Master

136 Posts

Posted - 2010-02-10 : 12:47:14

Truly amazing! – and I couldn’t even get my sample output correct.

Visakh – I went onto your Blog and have used your Calendar Table Function… and now have your T-SQL working with a copy of the real data.

I look forward to coming back into work tomorrow to make good use of the Outputs.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 13:00:21

quote:
Originally posted by DLTaylor

Truly amazing! – and I couldn’t even get my sample output correct.

Visakh – I went onto your Blog and have used your Calendar Table Function… and now have your T-SQL working with a copy of the real data.

I look forward to coming back into work tomorrow to make good use of the Outputs.

Thanks again!

Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP

sreenu9f
Yak Posting Veteran

73 Posts

Posted - 2011-02-21 : 10:22:39

Hi Visakh,
I am looking for similar output but instead of data i am looking at each hour and also the underlying table looks different here is how the table looks like with PK's AcctNo,ActDateTime, SeqID

AcctNo ActDateTime SeqID RoomID

123 2/11/11 09:10:00 1 4
123 2/11/11 15:10:00 2 6
123 2/12/11 08:00:00 1 5
456 2/11/11 10:00:00 1 6

the output should be
for date 2/11/11
RoomID @8 @9 @10 @11 @12 @13 @14 @15 @16 @17 @18....
4 1 1 1 1 1 1
6 1 1 1 1

for date 2/12/11
RoomID @8 @9 @10 @11 @12 @13 @14 @15 @16 @17 @18....
4
5 1 1 1 1 1 1 1 1 1
6 1 1 1 1 1 1 1 1....

Any help/Suggestion is greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources