Hospital Ward Occupancy - Grouped DateDiff? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Hospital Ward Occupancy - Grouped DateDiff?

Author

Topic

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-06 : 14:21:34

Hi

NOTE: Sample and Expected results scripts at end of text

Amendment: PLEASE USE THE EXPECTED RESULTS FUTHER DOWN THE PAGE AS I MADE A BIT OF A MISTAKE WITH THE SCRIPTED RESULTS

Hope someone can help with this as would be really useful for our hospital if we could calc this quickly via an SQL report.

The basic requirement is that we have patients staying on wards for a few days and then moving to another ward for a few days and then they can either move onto another ward or are discharged home.

I have done 2 scripts (at the end of this text) The first gives you the sample data I have so far and the second is an expected results table.

Notes on first table (sample data)

1) Each row represents a ward stay.
2) A patient can have more than one ward stay within an ADMISSION.
3) The order of the wards is reflected by POSITION. Where Position
=1 is the most recent and Max Position is the first ward.
4) DATEWARDCHANGE_DTE is the date that the patient enters the ward.
5) LOS is determined via the next DATEWARDCHANGE_DTE by the
subsequent position EXCEPT for the last ward where the DIS_DATE (discharge date)is used to calc the ward LOS.
6) CRN is the patient ID.

Notes on second table (expected results)

1) I am going to need a row for each date within any given range and
also a seperate row for each ward where there is activity in said
date range.
2) The COUNT is to be the number of patients that were on that ward
on that day.

Hope I have explained this well enough and if anyone has any questions please ask.

Thanks in advance

SAMPLE DATA SCRIPT

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BED_OCC](
[crn] [float] NULL,
[admission] [float] NULL,
[position] [float] NULL,
[ward] [nvarchar](255) NULL,
[hospital] [nvarchar](255) NULL,
[datewardchange_dte] [datetime] NULL,
[DIS_DATE] [datetime] NULL,
[next position] [float] NULL,
[LOS_ON_WARD] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (1234, 3, 1, N'WC', N'CIC', CAST(0x00009FD701194000 AS DateTime), CAST(0x00009FD800149970 AS DateTime), NULL, 1)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (1234, 3, 2, N'CCU', N'CIC', CAST(0x00009FD40107AC00 AS DateTime), NULL, 1, 3)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (5678, 14, 1, N'BB', N'CIC', CAST(0x00009FD300B54640 AS DateTime), CAST(0x00009FD30130DEE0 AS DateTime), NULL, 0)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (5678, 14, 2, N'CCU', N'CIC', CAST(0x00009FD2000C5C10 AS DateTime), NULL, 1, 1)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (5678, 14, 3, N'WC', N'CIC', CAST(0x00009FD001784820 AS DateTime), NULL, 2, 2)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (9999, 3, 1, N'BB', N'CIC', CAST(0x00009FD100D21D10 AS DateTime), CAST(0x00009FD10103D3A0 AS DateTime), NULL, 0)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (8888, 7, 1, N'BB', N'CIC', CAST(0x00009FDB00D5AF20 AS DateTime), CAST(0x00009FDC011826C0 AS DateTime), NULL, 1)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (7777, 5, 1, N'BB', N'CIC', CAST(0x00009FD0010FE960 AS DateTime), CAST(0x00009FD400D79B50 AS DateTime), NULL, 4)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (6666, 9, 1, N'CCU', N'CIC', CAST(0x00009FD000B964F0 AS DateTime), CAST(0x00009FD100A8A3E0 AS DateTime), NULL, 1)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (5555, 6, 1, N'WC', N'CIC', CAST(0x00009FD40107AC00 AS DateTime), CAST(0x00009FD50002BF20 AS DateTime), NULL, 1)

EXPECTED RESULTS TABLE

Amendment: PLEASE USE THE EXPECTED RESULTS FUTHER DOWN THE PAGE AS I MADE A BIT OF A MISTAKE WITH THE SCRIPTED RESULTS

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RESULTS](
[DATE ] [datetime] NULL,
[WARD] [nvarchar](255) NULL,
[COUNT ] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD000000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD000000000 AS DateTime), N'CCU', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD000000000 AS DateTime), N'BB', 2)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD100000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD100000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD100000000 AS DateTime), N'BB', 2)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD200000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD200000000 AS DateTime), N'CCU', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD200000000 AS DateTime), N'BB', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD300000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD300000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD300000000 AS DateTime), N'BB', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD400000000 AS DateTime), N'WC', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD400000000 AS DateTime), N'CCU', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD400000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD500000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD500000000 AS DateTime), N'CCU', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD500000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD600000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD600000000 AS DateTime), N'CCU', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD600000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD700000000 AS DateTime), N'WC', 1)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD700000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD700000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD800000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD800000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD800000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD900000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD900000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FD900000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDA00000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDA00000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDA00000000 AS DateTime), N'BB', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDB00000000 AS DateTime), N'WC', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDB00000000 AS DateTime), N'CCU', 0)
INSERT [dbo].[RESULTS] ([DATE ], [WARD], [COUNT ]) VALUES (CAST(0x00009FDB00000000 AS DateTime), N'BB', 1)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 15:09:36

something like


SELECT [Date],ward,COALESCE(Cnt,0) AS [Count]
FROM
(
SELECT f.[Date],t.ward
FROM dboCalendarTable(YourStartDate,YourEndDate,0,0) f
CROSS JOIN (SELECT DISTINCT ward FROM BED_OCC)t
)m
OUTER APPLY (SELECT COUNT(1) AS Cnt
             FROM BED_OCC
             WHERE ward = m.ward
             AND DIS_DATE=m.[Date])c

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-06 : 16:52:37

Please ignore my original results script. I have made a mistake.

The results should actually look as follows

Right I have the expected results here, please ignore the ones in the original post.

The results should look as follows

DATE WARD COUNT
06/01/2012 WC 1
06/01/2012 CCU 1
06/01/2012 BB 1
07/01/2012 WC 1
07/01/2012 CCU
07/01/2012 BB 1
08/01/2012 WC
08/01/2012 CCU 1
08/01/2012 BB 1
09/01/2012 WC
09/01/2012 CCU
09/01/2012 BB 1
10/01/2012 WC 1
10/01/2012 CCU 1
10/01/2012 BB
11/01/2012 WC
11/01/2012 CCU 1
11/01/2012 BB
12/01/2012 WC
12/01/2012 CCU 1
12/01/2012 BB
13/01/2012 WC 1
13/01/2012 CCU
13/01/2012 BB
14/01/2012 WC
14/01/2012 CCU
14/01/2012 BB
15/01/2012 WC
15/01/2012 CCU
15/01/2012 BB
16/01/2012 WC
16/01/2012 CCU
16/01/2012 BB
17/01/2012 WC
17/01/2012 CCU
17/01/2012 BB 1

Appologies once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 16:56:10

Did you check my suggestion? i think it should still give result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-06 : 17:37:08

Do i need to build a calender /dates table for your solution to work?

I just get Invalid object name 'dboCalendarTable'.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-06 : 18:24:43

yep you need to

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-07 : 08:44:16

Hi I have created a dates table and incorporated it into your query. I get some results :-)

You query seems to look at the dis_date where as we only need to calulate the days on that ward when it is in position 1 (ie they didnt move to another ward they left the hospital.

Example
Patient 5678 had 3 different wards.
They started on WC (position 3) for 2 days.
they then moved into CCU (position 2) for 1 night.
They then moved onto BB (position 1) but didnt stay the night thus LOS = 0 for the last ward.

Below is my query having added in the dates table.

I have just looked at one ward "CCU" for the time being to get that right first.

I would expect to see the following results.

TheDate ward Count
2012-01-06 CCU 1
2012-01-08 CCU 1
2012-01-10 CCU 1
2012-01-11 CCU 1 --at the moment i dont see this row
2012-01-12 CCU 1 --at the moment i dont see this row

This is your query that i have used but tweeked slightly. Hope it is not I who have caused the problem.

Once again thanks for your time and help

SELECT m.TheDate,ward,COALESCE(Cnt,0) AS [Count]
FROM

(
SELECT f.TheDate,t.ward
FROM dbo.tblDates f

CROSS JOIN

(SELECT DISTINCT ward FROM dbo.BED_OCC
where ward in ('CCU') )t
)m

OUTER APPLY (SELECT COUNT(1) AS Cnt
FROM dbo.BED_OCC
WHERE ward = m.ward
AND convert (nvarchar (100) , [datewardchange_dte], 23) = convert (nvarchar (100) , m.TheDate, 23)
--and datewardchange_dte >= '2012-01-01 00:00'
and m.ward in ('CCU')
--and convert (nvarchar (100) , m.TheDate, 23) >= '2012-01-01'

)c

where convert (nvarchar (100) , m.TheDate, 23) >= '2012-01-01'
and convert (nvarchar (100) , m.TheDate, 23) <= '2012-01-31'

and COALESCE(Cnt,0) > 0 -- just get data with a count against ward

order by m.TheDate , COALESCE(Cnt,0) desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 12:27:10

in case of patient movement you've one record per each movement right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-07 : 13:34:07

yes that is correct. one row of data represent one stay. the datewardchange_dte represents date into ward and the time spent on ward is determined by the subsiquent ward move OR dis_date dpending on wether patient goes to another ward or leaves hospital.

many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 13:37:47

then should my query suffice? didnt understand why you're not getting those two dates

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-07 : 14:58:28

hi

if you run the following agsinst a table of dates that cover jan 2012 you should get 31 rows for only Ward = CCU.
you will se that there was a patient on the ward on the 6th (pat 6666)for 1 night , 8th (pat 5678) for 1 night and the first days stay of (pat 1234) on the 10th. patient 1234 stayed on CCU for 3 days until they went to ward WC on the 13th. This makes a total of 5 days those 3 pateients spent on CCU.

Sorry if this is a bit of a hassle for you but i think its nearly there.

I would expect to see a count of 1 on the 6th, 8th, 10th, 11th aad 12th for ward = CCU.

SELECT m.TheDate,ward,COALESCE(Cnt,0) AS [Count]--, t.crn
FROM

(
SELECT f.TheDate,t.ward
FROM dbo.tblDates f

CROSS JOIN

(SELECT DISTINCT ward FROM dbo.bed_occ
where ward in ('ccu')
)t
)m

OUTER APPLY (SELECT COUNT(1) AS Cnt
FROM dbo.bed_occ
WHERE ward = m.ward
AND convert (nvarchar (100) , datewardchange_dte, 23) = convert (nvarchar (100) , m.TheDate, 23)
--and datewardchange_dte >= '2012-01-01 00:00'
and m.ward in ('ccu')
--and convert (nvarchar (100) , m.TheDate, 23) >= '2012-01-01'

)c

where convert (nvarchar (100) , m.TheDate, 23) >= '2012-01-01'
and convert (nvarchar (100) , m.TheDate, 23) <= '2012-01-31'

order by m.TheDate , COALESCE(Cnt,0) desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-07 : 15:14:47

Can you post how data will be in BED_OCC for patient 1234? will it have 1 record or three records?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice1
Starting Member

34 Posts

Posted - 2012-02-07 : 15:44:10

It will have 2 rows. As the patient has been in 2 wards.

A Patient has 1 crn , 1 admision number ( in this case 3) and can have many ward postions. (in this case 1 and 2, the highest of which is their first ward)

So this patient, all under admission 3 has 2 ward stays. His first on the 10th in CCU, 3 days later he moves into WC shown by the date he hit WC (datewardchange_dte on row 1). Hhis stay on CCU is is shown in expected results below.

He does have another ward after WC as he is discharged from hosp. (shown by the row with a dis_date)

What I'm trying to calc is the time spent on each ward but represented against the calender table. The intention is, by doing all this I will be able to see how many patients were on a ward at and given date.

data rows for patient 1234

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BED_OCC](
[crn] [float] NULL,
[admission] [float] NULL,
[position] [float] NULL,
[ward] [nvarchar](255) NULL,
[hospital] [nvarchar](255) NULL,
[datewardchange_dte] [datetime] NULL,
[DIS_DATE] [datetime] NULL,
[next position] [float] NULL,
[LOS_ON_WARD] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (1234, 3, 1, N'WC', N'CIC', CAST(0x00009FD701194000 AS DateTime), CAST(0x00009FD800149970 AS DateTime), NULL, 1)
INSERT [dbo].[BED_OCC] ([crn], [admission], [position], [ward], [hospital], [datewardchange_dte], [DIS_DATE], [next position], [LOS_ON_WARD]) VALUES (1234, 3, 2, N'CCU', N'CIC', CAST(0x00009FD40107AC00 AS DateTime), NULL, 1, 3)

expected results

TheDate ward Count
2012-01-10 CCU 1
2012-01-11 CCU 1
2012-01-12 CCU 1

If we were looking at Ward WC for this patient we woudl see the following results

TheDate ward Count
2012-01-13 WC 1

Thanks again for your help, much appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources