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 WARDCOUNT
06/01/2012WC0
06/01/2012CCU1
06/01/2012BB1
07/01/2012WC0
07/01/2012CCU0
07/01/2012BB1
08/01/2012WC0
08/01/2012CCU1
08/01/2012BB1
09/01/2012WC0
09/01/2012CCU0
09/01/2012BB1
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/2012WC1
06/01/2012CCU1
06/01/2012BB1
07/01/2012WC1
07/01/2012CCU
07/01/2012BB1
08/01/2012WC
08/01/2012CCU1
08/01/2012BB1
09/01/2012WC
09/01/2012CCU
09/01/2012BB1
10/01/2012WC1
10/01/2012CCU1
10/01/2012BB
11/01/2012WC
11/01/2012CCU1
11/01/2012BB
12/01/2012WC
12/01/2012CCU1
12/01/2012BB
13/01/2012WC1
13/01/2012CCU
13/01/2012BB
14/01/2012WC
14/01/2012CCU
14/01/2012BB
15/01/2012WC
15/01/2012CCU
15/01/2012BB
16/01/2012WC
16/01/2012CCU
16/01/2012BB
17/01/2012WC
17/01/2012CCU
17/01/2012BB1
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 wardCount -- notes
2012-01-06WC1 -- patient 5678 in on the 6th
2012-01-07WC1 -- patient 5678 in on the 7th
2012-01-10WC1 -- patient 5555 in on the 10th
2012-01-13WC1 -- 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