Sequence ID and data manipulation

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

SQL Server 2005 Forums

Transact-SQL (2005)

Sequence ID and data manipulation

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2013-01-11 : 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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-11 : 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]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-11 : 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

136 Posts

Posted - 2013-01-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2013-01-11 : 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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources