February 6, 2012 at 12:24 pm
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.
Amendment: PLEASE USE THE EXPECTED RESULTS FUTHER DOWN THE PAGE AS I MADE A BIT OF A MISTAKE WITH THE SCRIPTED RESULTS
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)
February 6, 2012 at 1:54 pm
Can you please verify that your expected results are based on the sample data?
Example:
I find only one row for ward='BB' that would match Jan'06. Why does your expected result needs to be "2"?
February 6, 2012 at 2:15 pm
Sorry my mistake.
As far as Ward = BB goes there shouuld only be patient CRN = "8888" in for one night on the 17th Jan and Patient "7777" in for 4 nihts from the 6th onwards.
DATE WARD COUNT
06/01/2012 WC 0
06/01/2012 CCU 1
06/01/2012 BB 1
07/01/2012 WC 0
07/01/2012 CCU 0
07/01/2012 BB 1
08/01/2012 WC 0
08/01/2012 CCU 1
08/01/2012 BB 1
09/01/2012 WC 0
09/01/2012 CCU 0
09/01/2012 BB 1
February 6, 2012 at 2:24 pm
Why does CCU has a count of 1 for Jan'08 but WC doesn't show 1 for Jan'06?
February 6, 2012 at 2:39 pm
Hi,
Basically I mucked up a bit with the expected results. I will try and re-do and re-post expected results
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.
Appologies
February 6, 2012 at 2:50 pm
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
Thanks again in advance for your patience. Parden the pun.:-)
February 6, 2012 at 3:00 pm
I'm still not sure how the expected results can be calculated (e.g. 07/01/2012 WC 1).
So I post what I have so far:
;
WITH cte_range AS
(
SELECT
CAST (MIN(datewardchange_dte) AS date) min_datewardchange_dte,
DATEDIFF(dd,MIN(datewardchange_dte),Max(datewardchange_dte)) AS cnt_datewardchange_dte
FROM [dbo].[BED_OCC]
),
cte_wards AS
(
SELECT DISTINCT ward
FROM [dbo].[BED_OCC]
),
cte_crosstab as
(
SELECT
DATEADD(dd,numbers.n-1,min_datewardchange_dte) AS datewardchange,
DATEADD(dd,numbers.n,min_datewardchange_dte) AS date_to,
ward
FROM cte_range
CROSS APPLY cte_wards
CROSS APPLY
(
SELECT TOP (cnt_datewardchange_dte+1) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS n
FROM sys.all_columns
)numbers
)
SELECT *
--cte.datewardchange, cte.ward, COUNT(crn)
FROM cte_crosstab cte
LEFT OUTER JOIN [dbo].[BED_OCC] bed
ON cte.date_to >= bed.datewardchange_dte
AND cte.date_to <= bed.dis_date
AND bed.ward = cte.ward
AND DATEDIFF(dd,bed.datewardchange_dte,dis_date)>0
--GROUP BY cte.datewardchange,cte.ward
ORDER BY cte.datewardchange,cte.ward
February 7, 2012 at 6:57 am
Thanks for your help,
the reason that on the 7th WC would count as 1 is because patient 5678 was on ward WC on the 6th , and 7th and then moved to ward CCU on the 8th.
When a patient doesn't have DIS_DATE on the same row then the period id calc'd from the date into the next ward (position)
The results I would expect to see for WC during this period is
TheDate ward Count -- notes
2012-01-06 WC 1 -- patient 5678 in on the 6th
2012-01-07 WC 1 -- patient 5678 in on the 7th
2012-01-10 WC 1 -- patient 5555 in on the 10th
2012-01-13 WC 1 -- patient 1234 in on the 13th
Once again Thanks for your help
February 7, 2012 at 1:07 pm
First of all, I'm very sorry it took me so long to understand what you're looking for. :blush:
I guess I got it now.
the adjustment I made to my previous code is the replacement of [BED_OCC] by a cte that is used to replace the NULL values with the datewardchange value of the next related ward.
If there's a performance issue with this query I recommend the following steps:
1) add an index to [BED_OCC] with datewardchange_dte and ward, include crn,dis_date
2) separate the calculation of min_datewardchange_dte and cnt_datewardchange_dte into a separate query
3) use a calendar table instead of the on-the-fly calculation
4) replace the NULL values in dis_date with the values as calculated in cte_dis_date_filled
;
WITH cte_range AS
-- find the first date and the number of days in the given range
-- if required, this should be replaced by corresponding variables
(
SELECT
CAST (MIN(datewardchange_dte) AS date) min_datewardchange_dte,
DATEDIFF(dd,MIN(datewardchange_dte),Max(datewardchange_dte)) AS cnt_datewardchange_dte
FROM [dbo].[BED_OCC]
),
cte_wards AS
-- list of all wards
(
SELECT DISTINCT ward
FROM [dbo].[BED_OCC]
),
cte_calendar as
-- calendar build on the fly with one day per date and ward in the given range
-- the numbers subquery should be replaced with a permanent numbers or tally table
(
SELECT
DATEADD(dd,numbers.n-1,min_datewardchange_dte) AS datewardchange,
DATEADD(dd,numbers.n,min_datewardchange_dte) AS date_to,
ward
FROM cte_range
CROSS APPLY cte_wards
CROSS APPLY
(
SELECT TOP (cnt_datewardchange_dte+1) ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS n
FROM sys.all_columns
)numbers
),
cte_dis_date_filled AS
-- fill the NULL values for dis_date with the corresponding value of the next related ward
(
SELECT
bed.datewardchange_dte,
bed.ward,
bed.crn,
ISNULL(bed.dis_date,x.datewardchange_dte) AS dis_date_fill
FROM [dbo].[BED_OCC] bed
OUTER APPLY
(
SELECT TOP 1 datewardchange_dte
FROM [dbo].[BED_OCC] bed2
WHERE bed.crn=bed2.crn AND bed2.datewardchange_dte>bed.datewardchange_dte
ORDER BY bed2.datewardchange_dte
)x
)
SELECT
cte.datewardchange,
cte.ward,
COUNT(crn) AS cnt
FROM cte_calendar cte
LEFT OUTER JOIN cte_dis_date_filled bed
ON cte.date_to >= bed.datewardchange_dte
AND cte.date_to <= bed.dis_date_fill
AND bed.ward = cte.ward
GROUP BY cte.datewardchange,cte.ward
ORDER BY cte.datewardchange,cte.ward
February 7, 2012 at 3:40 pm
Cheers I'll take a look tomoroow and let you know how i get on when using the main data set.]
Thanks again for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply