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 9 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply