Outer Apply/Cross apply or a join I haven't thought of?

Question

Outer Apply/Cross apply or a join I haven't thought of?

richardmgreen1

SSChampion

Points: 10356
More actions
January 17, 2014 at 8:05 am

#284639

Hi all
I'm working with healthcare data and need to get some data on hospital readmissions.
I want to get the persons ID number (UnitNumber), the original date they were admitted to the hospital (admitdatetime) and any subsequent admissions for the same person.
The order I need to get the data in is as follows:-
UnitNumber
admitdatetime
dischargedatetime (I can get that quite easily)
admitdatetime (for any subsequent admissions)
dischargedatetime (based on the second admitdatetime)
If there's a second readmission (so into hospital 3 times), I need a new record with the second admit/disharge date/time fields from above and the following admit/discharge date/times.
So, if a person goes into hospital 3 times, they would have two records as follows:-
Record 1:-
UnitNumber
first admitdatetime
first dischargedatetime
second admitdatetime
second dischargedatetime
Record 2:-
second admitdatetime (from record 1 above)
second dischargedatetime (from record 1 above)
third admitdatetime
third dischargedatetime
Hopefully all that makes sense.
The datetime fields are listed in the table as datetime datatypes and the Unitnumber is a varchar.
The table creation code is here:-
CREATE TABLE [dbo].[DailySnapshot_LIVE_RFT_DR_ABS_VIEW](
[UnitNumber] [varchar](30) NULL,
[AdmitDateTime] [datetime] NULL,
[DischargeDateTime] [datetime] NULL,
CONSTRAINT [PK_DailySnapshot_LIVE_RFT_DR_ABS_VIEW] PRIMARY KEY CLUSTERED
(
[ViewRecNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
There's a lot more on this table, but these are the relevant bits.
This is the code I have so far:-
set dateformat dmy
declare
@start datetime
,@end datetime
set @start='01/06/2012'
set @end='01/06/2013'
select distinct
--abs1.*
abs1.[UnitNumber] as Unit_Number
,abs1.visitid
,abs1.providerid as Provider_ID
,convert(varchar,abs1.admitdatetime,103) as Admission_Date
,convert(varchar,abs1.dischargedatetime,103) as Discharge_Date
,convert(varchar,abs2.admitdatetime,103) as Admission_Date
,convert(varchar,abs2.dischargedatetime,103) as Discharge_Date
,icd.ICD10Description
,datediff(d,convert(varchar,abs1.dischargedatetime,103),
convert(varchar,abs2.admitdatetime,103)) as [Readmit (Days)]
from
RFT_DR_VIEWS.dbo.DailySnapshot_LIVE_RFT_DR_ABS_VIEW abs1
inner join RFT_LOOKUPS.dbo.LU_DH_ICD10 icd
on abs1.primarydiagnosis=icd.ICD10AltCode COLLATE SQL_Latin1_General_CP1_CS_AS
inner join RFT_DR_VIEWS.dbo.DailySnapshot_LIVE_RFT_DR_ABS_VIEW abs2
on abs1.unitnumber=abs2.unitnumber
where
abs1.admitdatetime between @start and @end
and abs2.admitdatetime>abs1.admitdatetime
and abs2.visitid>abs1.visitid
and abs1.ptstatus='in'
AND (abs1.[Name] IS NULL OR abs1.[Name] NOT LIKE '%DONOTUSE%')
AND (abs1.[Name] IS NULL OR abs1.[Name] NOT LIKE '%YYTESTPATIENTII%')
AND (abs1.[Name] IS NULL OR abs1.[Name] NOT LIKE '%XXTESTPATIENT%')
AND abs1.Last_Episode_Indicator = 1
and abs1.[AdmissionWardLocationForSpell] like 'endo%'
and abs1.unitnumber='RU00000788'
and datediff(d,convert(varchar,abs1.dischargedatetime,103),
convert(varchar,abs2.admitdatetime,103))<35
order by
abs1.unitnumber
And based on the the query above, I get this:-
Unit_Number,visitid,Provider_ID,Admission_Date,Discharge_Date,Admission_Date,Discharge_Date,ICD10Description,Readmit (Days)
RU00000788,RA0-20121231161622417,HOEROLDTB,11/01/2013,11/01/2013,06/02/2013,06/02/2013,"Iron deficiency anaemia, unspecified",26
RU00000788,RA0-20121231161622417,HOEROLDTB,11/01/2013,11/01/2013,11/01/2013,11/01/2013,"Iron deficiency anaemia, unspecified",0
RU00000788,RA0-20121231161622417,HOEROLDTB,11/01/2013,11/01/2013,14/01/2013,14/01/2013,"Iron deficiency anaemia, unspecified",3
RU00000788,RA0-20121231161622417,HOEROLDTB,11/01/2013,11/01/2013,24/01/2013,24/01/2013,"Iron deficiency anaemia, unspecified",13
RU00000788,RA0-20121231161622417,HOEROLDTB,11/01/2013,11/01/2013,24/01/2013,25/01/2013,"Iron deficiency anaemia, unspecified",13
And last but not least, some data:-
select 'RU00000788','10/08/2012 10:34:00','23/08/2012 12:55:00'
select 'RU00000788','09/09/2012 23:31:00','09/09/2012 23:50:00'
select 'RU00000788','13/10/2013 23:54:00','14/10/2013 14:49:00'
select 'RU00000788','13/10/2013 23:54:00','14/10/2013 14:49:00'
select 'RU00000788','03/12/2013 11:00:00','03/12/2013 11:30:00'
select 'RU00000788','04/06/2012 03:10:00','04/06/2012 13:15:00'
select 'RU00000788','17/10/2012 16:17:00','17/10/2012 00:00:00'
select 'RU00000788','04/12/2012 22:29:00','05/12/2012 14:29:00'
select 'RU00000788','11/01/2013 08:36:00','11/01/2013 10:18:00'
select 'RU00000788','24/01/2013 11:59:00','24/01/2013 00:00:00'
select 'RU00000788','05/07/2012 17:16:00','05/07/2012 22:36:00'
select 'RU00000788','09/08/2012 12:26:00','09/08/2012 00:00:00'
select 'RU00000788','17/10/2012 15:33:00','17/10/2012 00:00:00'
select 'RU00000788','19/12/2012 16:08:00','19/12/2012 00:00:00'
select 'RU00000788','24/01/2013 16:24:00','25/01/2013 10:54:00'
select 'RU00000788','06/02/2013 15:49:00','06/02/2013 00:00:00'
select 'RU00000788','26/11/2013 21:55:00','03/12/2013 19:10:00'
select 'RU00000788','18/06/2012 22:29:00','19/06/2012 02:36:00'
select 'RU00000788','27/06/2012 21:56:00','28/06/2012 03:31:00'
select 'RU00000788','10/08/2012 08:17:00','10/08/2012 00:00:00'
select 'RU00000788','04/09/2012 22:33:00','05/09/2012 13:35:00'
select 'RU00000788','14/01/2013 14:47:00','14/01/2013 00:00:00'
select 'RU00000788','11/01/2013 15:20:00','11/01/2013 00:00:00'
select 'RU00000788','06/02/2013 14:10:00','06/02/2013 00:00:00'
select 'RU00000788','21/06/2013 16:04:00','21/06/2013 00:00:00'
select 'RU00000788','24/06/2013 09:32:00','24/06/2013 00:00:00'
select 'RU00000788','18/09/2013 03:21:00','18/09/2013 10:29:00'
select 'RU00000788','18/09/2013 03:21:00','18/09/2013 10:29:00'
Any help on this would be greatly appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

To help speed up the process can you convert you selects into inserts and also test your scripts (there are some errors) and last please provide the expected output based on your sample data.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

richardmgreen1 SSChampion Points: 10356 More actions · Answer 2

New table creation code is here:-

CREATE TABLE [dbo].[DailySnapshot_LIVE_RFT_DR_ABS_VIEW](

[UnitNumber] [varchar](30) NULL,

[AdmitDateTime] [datetime] NULL,

[DischargeDateTime] [datetime] NULL

CONSTRAINT [PK_DailySnapshot_LIVE_RFT_DR_ABS_VIEW] PRIMARY KEY CLUSTERED

(

[ViewRecNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Now the inserts:-

insert into DailySnapshot_LIVE_RFT_DR_ABS_VIEW(

[UnitNumber],

[AdmitDateTime],

[DischargeDateTime]

)

select 'RU00000788','10/08/2012 10:34:00','23/08/2012 12:55:00'

union

select 'RU00000788','09/09/2012 23:31:00','09/09/2012 23:50:00'

union

select 'RU00000788','13/10/2013 23:54:00','14/10/2013 14:49:00'

union

select 'RU00000788','13/10/2013 23:54:00','14/10/2013 14:49:00'

union

select 'RU00000788','03/12/2013 11:00:00','03/12/2013 11:30:00'

union

select 'RU00000788','04/06/2012 03:10:00','04/06/2012 13:15:00'

union

select 'RU00000788','17/10/2012 16:17:00','17/10/2012 00:00:00'

union

select 'RU00000788','04/12/2012 22:29:00','05/12/2012 14:29:00'

union

select 'RU00000788','11/01/2013 08:36:00','11/01/2013 10:18:00'

union

select 'RU00000788','24/01/2013 11:59:00','24/01/2013 00:00:00'

union

select 'RU00000788','05/07/2012 17:16:00','05/07/2012 22:36:00'

union

select 'RU00000788','09/08/2012 12:26:00','09/08/2012 00:00:00'

union

select 'RU00000788','17/10/2012 15:33:00','17/10/2012 00:00:00'

union

select 'RU00000788','19/12/2012 16:08:00','19/12/2012 00:00:00'

union

select 'RU00000788','24/01/2013 16:24:00','25/01/2013 10:54:00'

union

select 'RU00000788','06/02/2013 15:49:00','06/02/2013 00:00:00'

union

select 'RU00000788','26/11/2013 21:55:00','03/12/2013 19:10:00'

union

select 'RU00000788','18/06/2012 22:29:00','19/06/2012 02:36:00'

union

select 'RU00000788','27/06/2012 21:56:00','28/06/2012 03:31:00'

union

select 'RU00000788','10/08/2012 08:17:00','10/08/2012 00:00:00'

union

select 'RU00000788','04/09/2012 22:33:00','05/09/2012 13:35:00'

union

select 'RU00000788','14/01/2013 14:47:00','14/01/2013 00:00:00'

union

select 'RU00000788','11/01/2013 15:20:00','11/01/2013 00:00:00'

union

select 'RU00000788','06/02/2013 14:10:00','06/02/2013 00:00:00'

union

select 'RU00000788','21/06/2013 16:04:00','21/06/2013 00:00:00'

union

select 'RU00000788','24/06/2013 09:32:00','24/06/2013 00:00:00'

union

select 'RU00000788','18/09/2013 03:21:00','18/09/2013 10:29:00'

union

select 'RU00000788','18/09/2013 03:21:00','18/09/2013 10:29:00'

What I would want to see is as follows:-

UnitNumber______AdmitDateTime________DischargeDateTime_______ReadmitDateTime_________DischargeDateTime

RU00000788_____10/08/2012 10:34:00___23/08/2012 12:55:00_____09/09/2012 23:31:00______09/09/2012 23:50:00

RU00000788_____09/09/2012 23:31:00___09/09/2012 23:50:00_____13/10/2013 23:54:00______14/10/2013 14:49:00

and so on.

Hope that helps.

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 3

I updated your scripts to get rid of the error on PK and I also added more patients so that you can see the effect on multiple patients.

try this:

CREATE TABLE [dbo].[DailySnapshot_LIVE_RFT_DR_ABS_VIEW](

[UnitNumber] [varchar](30) NULL,

[AdmitDateTime] [datetime] NULL,

[DischargeDateTime] [datetime] NULL) ON [PRIMARY]

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'10/08/2012 10:34:00',103),convert(datetime,'23/08/2012 12:55:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'09/09/2012 23:31:00',103),convert(datetime,'09/09/2012 23:50:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'13/10/2013 23:54:00',103),convert(datetime,'14/10/2013 14:49:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'03/12/2013 11:00:00',103),convert(datetime,'03/12/2013 11:30:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'04/06/2012 03:10:00',103),convert(datetime,'04/06/2012 13:15:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'17/10/2012 16:17:00',103),convert(datetime,'17/10/2012 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'04/12/2012 22:29:00',103),convert(datetime,'05/12/2012 14:29:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'11/01/2013 08:36:00',103),convert(datetime,'11/01/2013 10:18:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'24/01/2013 11:59:00',103),convert(datetime,'24/01/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'05/07/2012 17:16:00',103),convert(datetime,'05/07/2012 22:36:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'09/08/2012 12:26:00',103),convert(datetime,'09/08/2012 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'17/10/2012 15:33:00',103),convert(datetime,'17/10/2012 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000788',convert(datetime,'19/12/2012 16:08:00',103),convert(datetime,'19/12/2012 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'24/01/2013 16:24:00',103),convert(datetime,'25/01/2013 10:54:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'06/02/2013 15:49:00',103),convert(datetime,'06/02/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'26/11/2013 21:55:00',103),convert(datetime,'03/12/2013 19:10:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'18/06/2012 22:29:00',103),convert(datetime,'19/06/2012 02:36:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'27/06/2012 21:56:00',103),convert(datetime,'28/06/2012 03:31:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'10/08/2012 08:17:00',103),convert(datetime,'10/08/2012 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'04/09/2012 22:33:00',103),convert(datetime,'05/09/2012 13:35:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000789',convert(datetime,'14/01/2013 14:47:00',103),convert(datetime,'14/01/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000781',convert(datetime,'11/01/2013 15:20:00',103),convert(datetime,'11/01/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000781',convert(datetime,'06/02/2013 14:10:00',103),convert(datetime,'06/02/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000781',convert(datetime,'21/06/2013 16:04:00',103),convert(datetime,'21/06/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000781',convert(datetime,'24/06/2013 09:32:00',103),convert(datetime,'24/06/2013 00:00:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000781',convert(datetime,'18/09/2013 03:21:00',103),convert(datetime,'18/09/2013 10:29:00',103)

insert dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW] select 'RU00000782',convert(datetime,'18/09/2013 03:21:00',103),convert(datetime,'18/09/2013 10:29:00',103);

with cte as

(

SELECT *, ROW_NUMBER() over (partition by unitnumber order by admitdatetime) as rn

FROM dbo.[DailySnapshot_LIVE_RFT_DR_ABS_VIEW]

)

SELECT a.UnitNumber, a.AdmitDateTime, a.DischargeDateTime, b.AdmitDateTime, b.DischargeDateTime

FROM cte a

left join cte b on a.UnitNumber = b.UnitNumber and b.rn = a.rn + 1

order by a.UnitNumber, a.AdmitDateTime

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I was going to post the same solution as Keith, but as long as I'm here, I would note that you might not need to use unitnumber column in partition by or join clause because you're filtering the unitnumber in your original query. That's up to you to test.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

richardmgreen1 SSChampion Points: 10356 More actions · Answer 5

Thanks folks, that looks like it's working perfectly.

Can someone please walk me through the code (I've never had much luck with CTEs)?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

I'll try to explain. You could think of a CTE as if it were a subquery. You can select the query in the cte to view what is the result of it. It's just selecting your table and adding a row_number to be able to join consecutive rows without problems of gaps or repeated values. The query itself is a self join of the table using the row number generated in the cte. It's a left join to avoid having a last row with values only in one set of columns.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

richardmgreen1 SSChampion Points: 10356 More actions · Answer 7

So the cte is almost acting as a temporary table having an insert done on it?

That makes sense.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

richardmgreen1 (1/20/2014)
So the cte is almost acting as a temporary table having an insert done on it?
That makes sense.

It's more like a single use view. Remember that even if it has table in its name, in the end, it's just an expression that return data.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2