| Author |
 Topic  |
|
|
DLTaylor
Posting Yak Master
United Kingdom
127 Posts |
 Posted - 01/11/2013 : 07:24:41
|
Hello All,
I’m using SQL 2005 sp3 compatibility level 90
I have a table, #InputData where I have data showing when a patient is admitted to a ward and when they leave a ward.
Within the table there are bed movements that create excessive rows of data.
Im looking to remove the bed movements from the data so I have just the ward start and ward end date time.
I have a solution that uses a CURSOR to LOOP through the data and creates the output I need…
However I’m sure the CURSOR is unnecessary and perhaps inefficient. Can you advise using the example below how I might be able to create the #OutputData seen in the example below.
Is it possible?
Any help would be great!
Thanks
--Sample TSQL
CREATE TABLE [dbo].[#InputData]
(
[VisitID] [varchar](30) NULL,
[AbsWardID] [varchar](10) NULL,
[AbsWardStartDateTime] [datetime] NULL,
[AbsWardEndDateTime] [datetime] NULL
)
Insert into #InputData
select 'X1009436410','MAU','2012-11-15 18:34:00.000','2012-11-15 19:46:00.000' UNION all
select 'X1009436410','MAU','2012-11-15 19:46:00.000','2012-11-15 20:41:00.000' UNION all
select 'X1009436410','MAU','2012-11-15 20:41:00.000','2012-11-15 21:15:00.000' UNION all
select 'X1009436410','MAU','2012-11-15 21:15:00.000','2012-11-15 21:49:00.000' UNION all
select 'X1009436410','MAU','2012-11-15 21:49:00.000','2012-11-16 13:38:00.000' UNION all
select 'X1009436410','44','2012-11-16 13:38:00.000','2012-11-16 14:09:00.000' UNION all
select 'X1009436410','44','2012-11-16 14:09:00.000','2012-11-20 16:15:00.000' UNION all
select 'X1009436410','44','2012-11-20 16:15:00.000','2012-11-20 21:51:00.000' UNION all
select 'X1009436410','HDU','2012-11-20 21:51:00.000','2012-11-21 11:26:00.000' UNION all
select 'X1009436410','HDU','2012-11-21 11:26:00.000','2012-11-21 16:50:00.000' UNION all
select 'X1009436410','44','2012-11-21 16:50:00.000','2012-11-21 17:14:00.000' UNION all
select 'X1009436410','44','2012-11-21 17:14:00.000','2012-11-26 16:09:00.000'
CREATE TABLE [dbo].[#OutputData]
(
[VisitID] [varchar](30) NULL,
[AbsWardID] [varchar](10) NULL,
[AbsWardStartDateTime] [datetime] NULL,
[AbsWardEndDateTime] [datetime] NULL
)
Insert into #OutputData
select 'X1009436410','MAU','2012-11-15 18:34:00.000','2012-11-16 13:38:00.000' UNION all
select 'X1009436410','44','2012-11-16 13:38:00.000','2012-11-20 21:51:00.000' UNION all
select 'X1009436410','HDU','2012-11-20 21:51:00.000','2012-11-21 16:50:00.000' UNION all
select 'X1009436410','44','2012-11-21 16:50:00.000','2012-11-26 16:09:00.000'
SELECT * FROM #InputData
SELECT * FROM #OutputData
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/11/2013 : 08:27:48
|
;with tstrt as
(
select t1.VisitID, t1.AbsWardID, t1.AbsWardStartDateTime
from #InputData t1
where not exists ( select *
from #InputData t2
where t1.VisitID = t2.VisitID
and t1.AbsWardID = t2.AbsWardID
and t2.AbsWardEndDateTime = t1.AbsWardStartDateTime
)
) ,
tend as
(
select t3.VisitID, t3.AbsWardID, t3.AbsWardEndDateTime
from #InputData t3
where not exists (
select *
from #InputData t2
where t3.VisitID = t2.VisitID
and t3.AbsWardID = t2.AbsWardID
and t3.AbsWardEndDateTime = t2.AbsWardStartDateTime
)
)
select tstrt.*, tend.AbsWardEndDateTime
from tstrt
join tend
on tstrt.VisitID = tend.VisitID
and tstrt.AbsWardID = tend.AbsWardID
and tend.AbsWardEndDateTime = ( select min(t2.AbsWardEndDateTime)
from tend t2
where t2.VisitID = tstrt.VisitID
and t2.AbsWardID = tstrt.AbsWardID
and t2.AbsWardEndDateTime > tstrt.AbsWardStartDateTime
)
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/11/2013 : 08:32:35
|
Nigel beat me to it, but since I spent time working on it, here is my solution  :SELECT
VisitID,
AbsWardID,
MIN([AbsWardStartDateTime]) AS [AbsWardStartDateTime],
MAX([AbsWardEndDateTime]) AS [AbsWardEndDateTime]
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY visitid ORDER BY abswardstartdatetime)
-ROW_NUMBER() OVER (PARTITION BY visitid,absWardId ORDER BY abswardstartdatetime) AS Grp
FROM
#InputData
) s
GROUP BY
VisitID,
AbsWardID,
Grp
ORDER BY
[AbsWardStartDateTime] |
Edited by - sunitabeck on 01/11/2013 08:33:18 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/11/2013 : 09:04:45
|
Difference is that depends on visitid,AbsWardID only. If there were two series for the same visitid and AbsWardID and no other visits in between it would think they were the same group even if there was a gap - e.g. if HDU was removed from the above data.
I think mine would give 2 series for 44 as it uses the start and end times.
Not sure which is wanted.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/11/2013 : 10:21:35
|
| I see what you mean. My guess is that the patient has to be in some ward or other without any gap in time for the entire duration of the visit. But I will defer to OP's OPinion on it. |
|
|
|
DLTaylor
Posting Yak Master
United Kingdom
127 Posts |
Posted - 01/11/2013 : 11:06:09
|
Firstly, apologies if this is a bit gushing…
This T-SQL is brilliant!!
I have been working through both solutions this afternoon and can add the following.
Both do exactly as requested!
The original CURSOR step took 3 hours to run processing 1.2m records per run (+ the table wasn’t well indexed etc..!)
Nigels’s solution – ran for me in 55 seconds
Sunitabecks solution - ran in in 9 seconds!
Looking at the comments after you are spot on … on further investigation a tiny number of records 1k from the 1.2m had times that didn’t quite flow (I didn’t know this during the original post!) and this explained the difference in the result set form both queries.
So I now have a number of data quality records to feedback (sarcastic…joy!)
And an brilliant new TSQL solution that saves my already overworked server (genuine …joy!)
Thanks for all your help
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 01/11/2013 : 13:06:35
|
quote:
Originally posted by DLTaylor
Firstly, apologies if this is a bit gushing…
This T-SQL is brilliant!!
Glad you got it to work - BUT:
Don't apologize! Gush as much as you like (unless Nigel objects) 
|
|
|
| |
Topic |
|
|
|
Sequence ID and data manipulation
