SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Sequence ID and data manipulation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DLTaylor
Posting Yak Master

United Kingdom
136 Posts

Posted - 01/11/2013 :  07:24:41  Show Profile  Reply with Quote
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
3383 Posts

Posted - 01/11/2013 :  08:27:48  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
;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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/11/2013 :  08:32:35  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/11/2013 :  09:04:45  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/11/2013 :  10:21:35  Show Profile  Reply with Quote
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.
Go to Top of Page

DLTaylor
Posting Yak Master

United Kingdom
136 Posts

Posted - 01/11/2013 :  11:06:09  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 01/11/2013 :  13:06:35  Show Profile  Reply with Quote
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)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000