Hi,
Any one try to help me on collapsing the date logic from the below example:
code:
Declare @Mem table (Memid varchar(100),Fullname varchar(100),Diagcode varchar(100),AdmitDate datetime,DateofDischarge datetime)
insert into @Mem
select 'M001','AA','41','2015-07-07 00:00:00','2015-07-13 00:00:00' union all
select 'M001','AA','41.0','2015-07-14 00:00:00','2015-07-18 00:00:00' union all
select 'M002','bb','35.00','2015-08-29 00:00:00','2015-08-31 00:00:00' union all
select 'M002','bb','35.00','2015-08-30 00:00:00','2015-08-30 00:00:00' union all
select 'M002','bb','35.00','2015-08-30 00:00:00','2015-08-30 00:00:00'
select * from @Mem
SELECT s1.Memid,s1.FullName,s1.Diagcode
,s1.[AdmitDate]
,MIN(t1.[DateOfDischarge]) AS [DateOfDischarge]
FROM @Mem s1
INNER JOIN @Mem t1 ON (t1.memid=s1.memid AND s1.[AdmitDate] <= t1.[DateOfDischarge])
AND NOT EXISTS(
SELECT * FROM @Mem t2
WHERE t2.memid=t1.memid AND (t1.[DateOfDischarge]+1) >= t2.[AdmitDate] AND t1.[DateOfDischarge] < t2.[DateOfDischarge]
)
WHERE NOT EXISTS(
SELECT * FROM @Mem s2
WHERE s2.memid=s1.memid
AND s1.[AdmitDate] > s2.[AdmitDate]
AND (s1.[AdmitDate]-1) <= s2.[DateOfDischarge]
)
group by s1.Memid,s1.FullName
,s1.[AdmitDate],s1.Diagcode
wrong output
Memid FullName DiagCode AdmitDate DateOfDischarge
M001 AA 401.0 2015-07-07 00:00:00 2015-07-18 00:00:00
M002 bb 403.00 2015-08-29 00:00:00 2015-08-31 00:00:00
Expected output
Memid FullName DiagCode AdmitDate DateOfDischarge
M001 AA 401.0 2015-07-07 00:00:00 2015-07-13 00:00:00
M001 AA 403.90 2015-07-14 00:00:00 2015-07-18 00:00:00
M002 bb 403.00 2015-08-29 00:00:00 2015-08-31 00:00:00
when 'AA' member is discharged and if he readmitted on the next day then two line item should be shown. but in my query its merging.
for 'BB' Member collapse is happening correctly.