SQLTeam.com | Weblogs | Forums

30 days followup admit into single line


#1

[code]
Declare @Hosptial table
(
Membno int,
clientlname varchar(100),
clientfname varchar(100),
svccod varchar(10),
admitdat datetime,
dischrgdat datetime
)

insert into @Hosptial
select 1,'AAA','AAAFname','012','2016-11-03 00:00:00.000','2016-11-09 00:00:00.000' union all
select 2,'BBB','BBBFname','012','2016-08-02 00:00:00.000','2016-08-04 00:00:00.000' union all
select 2,'BBB','BBBFname','012','2016-10-13 00:00:00.000','2016-10-23 00:00:00.000' union all
select 2,'BBB','BBBFname','012','2016-11-23 00:00:00.000','2016-12-07 00:00:00.000' union all
select 3,'CCC','CCCFname','012','2016-10-04 00:00:00.000','2016-10-10 00:00:00.000' union all
select 4,'DDD','DDDFname','012','2016-08-31 00:00:00.000','2016-09-08 00:00:00.000' union all
select 5,'EEE','EEEFname','012','2016-10-18 00:00:00.000','2016-10-24 00:00:00.000'

[code]

select * from @Hosptial
Current output:

Membno	clientlname	clientfname	svccod	admitdat	dischrgdat
1	AAA	AAAFname	012	2016-11-03 00:00:00.000	2016-11-09 00:00:00.000
1	AAA	AAAFname	012	2016-12-02 00:00:00.000	2016-12-07 00:00:00.000
2	BBB	BBBFname	012	2016-08-02 00:00:00.000	2016-08-04 00:00:00.000
2	BBB	BBBFname	012	2016-10-13 00:00:00.000	2016-10-23 00:00:00.000
2	BBB	BBBFname	012	2016-11-23 00:00:00.000	2016-12-07 00:00:00.000
3	CCC	CCCFname	012	2016-10-04 00:00:00.000	2016-10-10 00:00:00.000
4	DDD	DDDFname	012	2016-08-31 00:00:00.000	2016-09-08 00:00:00.000
5	EEE	EEEFname	012	2016-10-18 00:00:00.000	2016-10-24 00:00:00.000
5	EEE	EEEFname	012	2016-11-11 00:00:00.000	2016-11-21 00:00:00.000
Expected output:

Membno	clientlname	clientfname	svccod	admitdat				dischrgdat				[Re-Admit within 30 days]	[Re-Admit discharge Date]
1		AAA			AAAFname	012		2016-11-03 00:00:00.000	2016-11-09 00:00:00.000	2016-12-02 00:00:00.000		2016-12-07 00:00:00.000
2		BBB			BBBFname	012		2016-08-02 00:00:00.000	2016-08-04 00:00:00.000		
2		BBB			BBBFname	012		2016-10-13 00:00:00.000	2016-10-23 00:00:00.000		
2		BBB			BBBFname	012		2016-11-23 00:00:00.000	2016-12-07 00:00:00.000		
3		CCC			CCCFname	012		2016-10-04 00:00:00.000	2016-10-10 00:00:00.000		
4		DDD			DDDFname	012		2016-08-31 00:00:00.000	2016-09-08 00:00:00.000		
5		EEE			EEEFname	012		2016-10-18 00:00:00.000	2016-10-24 00:00:00.000	2016-11-11 00:00:00.000		2016-11-21 00:00:00.000

need to bring the 30 days readmit logic as single line


#2

This may do it, but I'm not sure that your input data matches your expected results. (e.g. why do you have to lines for member 1? There's only one data row for that member in your input data that I can see.)

select Membno, clientlname, clientfname, svccod, h1.admitdat, h1.dischrgdat, h2.admitdat, h2.dischrgdat
from @Hosptial h1
outer apply
(
select h2.admitdat, h2.dischrgdat
from @Hosptial h2
where h1.Membno = h2.Membno
and h1.admitdat <> h2.admitdat
and 30 >= abs(datediff(day, h1.admitdat, h2.admitdat))
) h2

#3

but the reverse data also comes from your output for the member 1 and 5

member 1 discharged at '2016-11-09' and Readmit after 30 days is '2016-12-02 00:00:00.000' which is correct in your query. but for the same member line item:2
discharged as '2016-12-07 00:00:00.000' and readmit after 30 days takes '2016-11-03 00:00:00.000' it should be null.

Membno	clientlname	clientfname	svccod	admitdat	dischrgdat	admitdat	dischrgdat
1	AAA	AAAFname	012	2016-11-03 00:00:00.000	2016-11-09 00:00:00.000	2016-12-02 00:00:00.000	2016-12-07 00:00:00.000
1	AAA	AAAFname	012	2016-12-02 00:00:00.000	2016-12-07 00:00:00.000	2016-11-03 00:00:00.000	2016-11-09 00:00:00.000
2	BBB	BBBFname	012	2016-08-02 00:00:00.000	2016-08-04 00:00:00.000	NULL	NULL
2	BBB	BBBFname	012	2016-10-13 00:00:00.000	2016-10-23 00:00:00.000	NULL	NULL
2	BBB	BBBFname	012	2016-11-23 00:00:00.000	2016-12-07 00:00:00.000	NULL	NULL
3	CCC	CCCFname	012	2016-10-04 00:00:00.000	2016-10-10 00:00:00.000	NULL	NULL
4	DDD	DDDFname	012	2016-08-31 00:00:00.000	2016-09-08 00:00:00.000	NULL	NULL
5	EEE	EEEFname	012	2016-10-18 00:00:00.000	2016-10-24 00:00:00.000	2016-11-11 00:00:00.000	2016-11-21 00:00:00.000
5	EEE	EEEFname	012	2016-11-11 00:00:00.000	2016-11-21 00:00:00.000	2016-10-18 00:00:00.000	2016-10-24 00:00:00.000

#4

the drop the abs()