Nightmare.. SQL statment Help !!

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 2008 Forums

Transact-SQL (2008)

Nightmare.. SQL statment Help !!

Author

Topic

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-16 : 10:32:50

Hi

I have got a table which holds records for each time a patient was admitted along with discharge date if discharge, null if not and total duration between admission and discharge(if null then current date). Here it holds the ward location when patient was admitted first time round. One thing about these patients they can stay for a very long period stretching over years. Let’s call this InpatientTable

However, during the stay patient gets transferred from one ward to another and that table contains multiple records relating to one record belonging to InpatientTable, it consist primary key, transfer date, transfer from ward, transfer to ward.

Now I need to create a composite table which gives me multiple records due to ward transfer along with duration for each stay within.

Any ideas, help, nuggets much appreciated....

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-16 : 11:02:12

[code]
Set NoCount On

Declare @p table ( --Patient
pId int identity(1,1),
name varchar(10),
AdmittDate datetime,
DischargeDate datetime
)

Insert Into @p Select 'Bob', '1/1/2007', '2/1/2011'
Insert Into @p Select 'Sally', '4/23/2009', null

Declare @w table ( --Ward
wId int identity(1,1),
Ward varchar(10)
)

Insert Into @w Select 'general'
Insert Into @w Select 'isolation'
Insert Into @w Select 'maternity'
Insert Into @w Select 'prison'

Declare @wt table ( --Ward Transfer
wtId int identity(1,1),
pId int,
wtDate datetime,
fromWId int,
toWId int
)

Insert Into @wt Select 1, '1/1/2007', null, 1
Insert Into @wt Select 1, '6/17/2008', 1, 4
Insert Into @wt Select 1, '4/23/2009', 4, 2
Insert Into @wt Select 2, '4/23/2009', null, 3
Insert Into @wt Select 1, '11/05/2010', 2, 1
Insert Into @wt Select 2, '11/17/2010', 3, 1

Set NoCount Off

;with cte1 As (
Select *, twn = Row_Number()Over(Partition By pId Order By wtDate) From @wt
), cte2 As (
Select
A.pId,
WardAdmittDate = A.wtDate,
WardDischargeDate = B.wtDate,
wId = A.toWId
From cte1 A
Left Join cte1 B
On A.pId = B.pId
and A.twn = B.twn - 1
)

Select
A.pId,
A.name,
B.wId,
C.Ward,
WardAdmittDate,
WardDischargeDate = isnull(B.WardDischargeDate,A.DischargeDate),
DaysIn = DATEDIFF(dy,WardAdmittDate,isnull(B.WardDischargeDate,A.DischargeDate))
From @p A
Inner Join cte2 B
On A.pId = B.pId
Inner Join @w C
On B.wId = C.wId
[/code]

Corey

I Has Returned!!

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-16 : 11:20:11

Oh my god, I nearly choked on my sandwich and literally spilled my drink all over my desk when I saw your reply and how it worked like a treat. That’s the reason it took a while to reply as I was cleaning my desk.
Gee Corey aka Seventhnight, you cracked it open like it’s just a piece of cake.

Much much much appreciated.

Thank you very very much.

Cheers

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-16 : 11:22:46

Glad it helps!

Hope I didn't cause any damage by way of food

Corey

I Has Returned!!

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-16 : 11:44:00

No Corey it didn't.

Just nearly ruined my work laptop, which is tiny bit costly as I run a lot of process hungry apps and old one was not upto job, so I moaned for about 10 months to get hold of this laptop.

I will call up on you help in future, if you don't mind.

thank you once again.

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-17 : 07:01:33

Hi Corey

What if .......

@p table ( --Patient
pId int identity(1,1),
name varchar(10),
AdmittDate datetime,
DischargeDate datetime,
wId int
)

Insert Into @p Select 'Bob', '1/1/2007', '2/1/2011', 1
Insert Into @p Select 'Sally', '4/23/2009', null, 3

----------
Because, in my ward transfer table, i do not have the following entries,

Table - @wt

Insert Into @wt Select 1, '1/1/2007', null, 1
Insert Into @wt Select 2, '4/23/2009', null, 3

instead, it is in my main patient table ( here @p) as above..

as not having the above two entries in @wt table misses the initial stay before the very first ward transfer....

Any ideas.............

Thanks mate

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-17 : 07:54:18

Too bad we other (the non-Corey's) cannot help out

N 56°04'39.26"
E 12°55'05.63"

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-17 : 08:00:59

Hi SwePeso

No offence mate.. Don’t take it personally.

It’s just he helped me and thought, he might overlook as I said it worked, and indeed it worked but need some twicking. Corey seems a very genuine fella.

I will still be your mate; at least you bother to reply. Every effort is appreciated.

Thank you

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 08:29:51

quote:
Originally posted by SwePeso

Too bad we other (the non-Corey's) cannot help out

N 56°04'39.26"
E 12°55'05.63"

You're more than welcome to chime in as far as I am concerned. I dare say I can't begin to compete with some of you guys

Corey

I Has Returned!!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 08:30:23

Updated to have the first Ward on the patient record:


Set NoCount On 

Declare @p table ( --Patient
	pId int identity(1,1),
	name varchar(10),
	AdmittDate datetime,
	DischargeDate datetime,
	wId int
)

Insert Into @p Select 'Bob', '1/1/2007', '2/1/2011', 1
Insert Into @p Select 'Sally', '4/23/2009', null, 3

Declare @w table ( --Ward
	wId int identity(1,1),
	Ward varchar(10)
)

Insert Into @w Select 'general'
Insert Into @w Select 'isolation'
Insert Into @w Select 'maternity'
Insert Into @w Select 'prison'

Declare @wt table ( --Ward Transfer
	wtId int identity(1,1),
	pId int,
	wtDate datetime,
	fromWId int,
	toWId int
)

--Insert Into @wt Select 1, '1/1/2007', null, 1
Insert Into @wt Select 1, '6/17/2008', 1, 4
Insert Into @wt Select 1, '4/23/2009', 4, 2
--Insert Into @wt Select 2, '4/23/2009', null, 3
Insert Into @wt Select 1, '11/05/2010', 2, 1
Insert Into @wt Select 2, '11/17/2010', 3, 1

Set NoCount Off


;with cte1 As (
	Select *, twn = Row_Number()Over(Partition By pId Order By wtDate) 
	From 
		(
		Select * From @wt
		Union All
		Select wtId = 0-ROW_NUMBER()Over(Order By pId), pId, wtDate = AdmittDate, fromWId = null, toWId = wId From @p
		) A
		
), cte2 As (
	Select 
		A.pId,
		WardAdmittDate = A.wtDate,
		WardDischargeDate = B.wtDate,
		wId = A.toWId
	From cte1 A
	Left Join cte1 B
	On A.pId = B.pId
	and A.twn = B.twn - 1
)

Select 
	A.pId,
	A.name,
	B.wId,
	C.Ward,
	WardAdmittDate,
	WardDischargeDate = isnull(B.WardDischargeDate,A.DischargeDate),
	DaysIn = DATEDIFF(dy,WardAdmittDate,isnull(B.WardDischargeDate,A.DischargeDate))
From @p A
Inner Join cte2 B
On A.pId = B.pId
Inner Join @w C
On B.wId = C.wId

Corey

I Has Returned!!

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-17 : 10:01:11

Morning Corey

Thank you very much mate. It worked perfectly. I had to add new identity field to my wardTransfer table. It was bit of pain as table was derived form system and had to twick the extraction script to add new field.

Apologies for putting you in awkward position here, didn't mean to.

Massive thanks to you for helping me once again.

Sap

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-17 : 11:20:54

What If from the output of this table

pId name wId Ward WardAdmittDate WardDischargeDate DaysIn
1 Bob 1 general 2007-01-01 2008-06-17 533
1 Bob 4 prison 2008-06-17 2009-04-23 310
1 Bob 2 isolation 2009-04-23 2010-11-05 561
1 Bob 1 general 2010-11-05 2011-02-01 88
2 Sally 3 maternity 2009-04-23 2010-11-17 573
2 Sally 1 general 2010-11-17 NULL NULL

If I want to attach what group they belong to from the table below

pId GroupStartDate GroupEndDate Group
1 2007-08-01 2008-12-01 1
1 2008-12-02 2010-06-01 5
2 2009-05-01 2010-01-01 8
2 2010-01-02 2011-03-31 10

Any period not covered by group table mean they belong to group 99

Any idea...???!!!

Thank you

PS: don't know why table lost the formatting

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 11:34:00

quote:
PS: don't know why table lost the formatting

you have to wrap Code and 'formatted' text in [Code]...[/Code]

So what should the final result look like? Do you want date ranges for <Ward,Group> pairs?

For example, for pId=1, the group rows both overlap the second ward row...

Corey

I Has Returned!!

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-17 : 11:38:40

Thanks, I will remember to wrap it next time, I am very new to this forum.

Yes you got it right, date ranges for <Ward,Group> Pairs.

So, a ward admissission may belong to two groups, which will in turn return two records for a single stay. Admdate and dischargedate will be affected by this.

Cheers

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 12:15:44

Lot more work to do multiple combinations of overlapping dates:


Set NoCount On 

Declare @p table ( --Patient
	pId int identity(1,1),
	name varchar(10),
	AdmittDate datetime,
	DischargeDate datetime,
	wId int
)

Insert Into @p Select 'Bob', '1/1/2007', '2/1/2011', 1
Insert Into @p Select 'Sally', '4/23/2009', null, 3

Declare @w table ( --Ward
	wId int identity(1,1),
	Ward varchar(10)
)

Insert Into @w Select 'general'
Insert Into @w Select 'isolation'
Insert Into @w Select 'maternity'
Insert Into @w Select 'prison'

Declare @wt table ( --Ward Transfer
	wtId int identity(1,1),
	pId int,
	wtDate datetime,
	fromWId int,
	toWId int
)

--Insert Into @wt Select 1, '1/1/2007', null, 1
Insert Into @wt Select 1, '6/17/2008', 1, 4
Insert Into @wt Select 1, '4/23/2009', 4, 2
--Insert Into @wt Select 2, '4/23/2009', null, 3
Insert Into @wt Select 1, '11/05/2010', 2, 1
Insert Into @wt Select 2, '11/17/2010', 3, 1

Declare @g table (
	pId int,
	GroupStartDate datetime,
	GroupEndDate datetime,
	gId int
)

Insert Into @g Select 1, '2007-08-01', '2008-12-01', 1
Insert Into @g Select 1, '2008-12-02', '2010-06-01', 5
Insert Into @g Select 2, '2009-05-01', '2010-01-01', 8
Insert Into @g Select 2, '2010-01-02', '2011-03-31', 10

Set NoCount Off

;with cte1 As (
	Select *, rn = Row_Number()Over(Partition By pId Order By wtDate) 
	From 
		(
		Select * From @wt
		Union All
		Select wtId = 0-ROW_NUMBER()Over(Order By pId), pId, wtDate = AdmittDate, fromWId = null, toWId = wId From @p
		) A
		
), cte2 As (
	Select 
		A.pId,
		WardAdmittDate = A.wtDate,
		WardDischargeDate = B.wtDate,
		wId = A.toWId
	From cte1 A
	Left Join cte1 B
	On A.pId = B.pId
	and A.rn = B.rn - 1
), cte3 As (
	Select pId, pDate = wtDate From @wt Where wtDate is not null
	Union 
	Select pId, pDate = AdmittDate From @p Where AdmittDate is not null
	Union 
	Select pId, pDate = DischargeDate From @p Where DischargeDate is not null
	Union 
	Select pId, pDate = GroupStartDate From @g Where GroupStartDate is not null
	Union 
	Select pId, pDate = GroupEndDate From @g Where GroupEndDate is not null
)
, cte4 as (
	Select 
		A.pId, 
		A.pDate,
		wId = B.wId,
		gId = isnull(C.gId,99),
		rn = ROW_NUMBER() Over(Partition By A.pId Order By A.pDate)
	From cte3 A
	Left Join cte2 B
	On A.pId = B.pId
	and A.pDate >= B.WardAdmittDate
	and A.pDate < isnull(B.WardDischargeDate,A.pDate+1)
	Left Join @g C
	On A.pId = C.pId
	and A.pDate >= C.GroupStartDate
	and A.pDate < C.GroupEndDate
) 

Select 
	A.pId,
	C.name,
	D.ward,
	grp = A.gId,
	StartDate = A.pDate,
	EndDate = B.pDate,
	DayCnt = DATEDIFF(dy, A.pDate, B.pDate),
	A.wId,
	A.gid
From cte4 A
Inner Join cte4 B
On A.pId = B.pId
and A.rn = B.rn - 1
Inner Join @p C
On A.pId = C.pId
Inner Join @w D
On A.wId = D.wId

Corey

I Has Returned!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-17 : 15:59:06

Even though my name is not Corey, I finally felt obligated to give the problem a try.

-- Create a personal date helper table
CREATE TABLE	#Dates
		(
			RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
			pID INT NOT NULL,
			StartDate DATE NOT NULL,
			EndDate DATE,
			DayCnt AS DATEDIFF(DAY, StartDate, EndDate),
			wID INT,
			gID INT
		)

-- Populate date helper table
INSERT		#Dates
		(
			pID,
			StartDate
		)
SELECT		pID,
		StartDate
FROM		(
			SELECT	u.pID,
				u.StartDate
			FROM	@p AS p
			UNPIVOT	(
					StartDate
					FOR theType IN (p.AdmittDate, p.DischargeDate)
				) AS u

			UNION

			SELECT	u.pID,
				u.StartDate
			FROM	@g AS g
			UNPIVOT	(
					StartDate
					FOR theType IN (g.GroupStartDate, g.GroupEndDate)
				) AS u

			UNION

			SELECT	pID,
				wtDate AS StartDate
			FROM	@wt
		) AS d
ORDER BY	pID,
		StartDate

-- Calculate correct interval
UPDATE		d
SET		d.EndDate = x.StartDate
FROM		#Dates AS d
CROSS APPLY	(
			SELECT	x.StartDate
			FROM	#Dates AS x
			WHERE	x.pID = d.pID
				AND x.RowID = d.RowID + 1
		) AS x(StartDate)

-- Remove incomplete intervals
DELETE
FROM	#Dates
WHERE	EndDate IS NULL

-- Find the ward moved to
UPDATE		d
SET		d.wID = wt.ToWID
FROM		#Dates AS d
INNER JOIN	@wt AS wt ON wt.pID = d.pID
			AND wt.wtDate = d.StartDate
WHERE		d.wID IS NULL

-- Find the ward moved from
UPDATE		d
SET		d.wID = wt.FromWID
FROM		#Dates AS d
INNER JOIN	@wt AS wt ON wt.pID = d.pID
			AND wt.wtDate = d.EndDate
WHERE		d.wID IS NULL

-- Calculate ward for intermediate events
UPDATE		d
SET		d.wID = x.wID
FROM		#Dates AS d
CROSS APPLY	(
			SELECT TOP(1)	x.wID
			FROM		#Dates AS x
			WHERE		x.pID = d.pID
					AND x.RowID > d.RowID
					AND x.wID IS NOT NULL
			ORDER BY	x.RowID
		) AS x(wID)
WHERE		d.wID IS NULL

-- Calculate ward for intermediate events
UPDATE		d
SET		d.wID = x.wID
FROM		#Dates AS d
CROSS APPLY	(
			SELECT TOP(1)	x.wID
			FROM		#Dates AS x
			WHERE		x.pID = d.pID
					AND x.RowID < d.RowID
					AND x.wID IS NOT NULL
			ORDER BY	x.RowID DESC
		) AS x(wID)
WHERE		d.wID IS NULL

-- Calculate group
UPDATE		d
SET		d.gID = x.gID
FROM		#Dates AS d
CROSS APPLY	(
			SELECT	g.gID
			FROM	@g AS g
			WHERE	g.pID = d.pID
				AND d.StartDate BETWEEN g.GroupStartDate AND g.GroupEndDate
				AND d.EndDate BETWEEN g.GroupStartDate AND g.GroupEndDate
		) AS x(gID)

-- Display the result
SELECT		p.pID,
		p.Name,
		w.Ward,
		COALESCE(d.gID, 99) AS grp,
		d.StartDate,
		d.EndDate,
		d.DayCnt,
		d.wID,
		COALESCE(d.gID, 99) AS gID
FROM		#Dates AS d
INNER JOIN	@p AS p ON p.pID = d.pID
INNER JOIN	@w AS w ON w.wID = d.wID
ORDER BY	d.RowID

-- Clean up
DROP TABLE	#Dates

N 56°04'39.26"
E 12°55'05.63"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-08-17 : 16:24:15

quote:
Originally posted by SwePeso

Even though my name is not Corey, I finally felt obligated to give the problem a try.

I knew you wouldn't be able to restrain yourself...

Corey

I Has Returned!!

Saphelp178
Starting Member

14 Posts

Posted - 2011-08-18 : 05:24:02

Thank you very much both Corey and Peter.

What really amazes me is how two diff logic produces same result.

I have to say, you guys have saved me a good deal of time and above all I learnt so much. My background is from SAP FICO and having seen you guys in action with your sql skills have helped me get my teeth into it.

Much much appreciated Peter & Corey.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-08-18 : 07:00:26

To be honest, they have the same logic. Just different approaches.
And for performance... Well, I bet my suggestion performs a fraction faster than Corey's due the I don't have a full triangular join.

N 56°04'39.26"
E 12°55'05.63"

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-18 : 12:54:42

The worst part is, they BOTH did the code off the top of their heads without any effort!!!!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-08-18 : 14:55:01

quote:
Originally posted by DonAtWork

The worst part is, they BOTH did the code off the top of their heads without any effort!!!!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

You can't be a SQL God if you make it look hard, but there was probably some effort involved.

CODO ERGO SUM

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources