[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