insert into @NFWOClaimid
SELECT 'memA','SMIT','2015-02-03 00:00:00.000','2015-07-31 00:00:00.000'
insert into @NFAcuteIPWOClaimID
SELECT 'memA','SMIT','2015-06-23 00:00:00.000','2015-06-26 00:00:00.000'
union all
SELECT 'memA','SMIT','2015-07-29 00:00:00.000','2015-07-31 00:00:00.000'
Expected output:
by comparing the two two set with MEMiID and AcuteIPAdmitDate falls between NFAdmitDate and NFEndDate the below resultset should be shown:
This is a total hack but like my mom says about me, it works. I am sure there are better ways to do it using the window functions on newer versions of SQL Server but this was run on 2008 R2 so it should run on most systems.
Declare @NFWOClaimid TABLE(MemID VARCHAR(100),FULLNAME VARCHAR(100),NFAdmitDate datetime,NFEndDate datetime)
Declare @NFAcuteIPWOClaimID TABLE(MemID VARCHAR(100),FULLNAME VARCHAR(100),AcuteIPAdmitDate datetime,AcuteIPEndDate datetime)
insert into @NFWOClaimid
SELECT 'memA','SMIT','2015-02-03 00:00:00.000','2015-07-31 00:00:00.000'
insert into @NFAcuteIPWOClaimID
SELECT 'memA','SMIT','2015-06-23 00:00:00.000','2015-06-26 00:00:00.000'
union all
SELECT 'memA','SMIT','2015-07-29 00:00:00.000','2015-07-31 00:00:00.000'
;WITH cteData As
(
SELECT a.MemID,
a.FULLNAME,
a.NFAdmitDate As DateValue
FROM @NFWOClaimid As a
UNION ALL
SELECT a.MemID,
a.FULLNAME,
a.NFEndDate As DateValue
FROM @NFWOClaimid As a
UNION ALL
SELECT a.MemID,
a.FULLNAME,
a.AcuteIPAdmitDate As DateValue
FROM @NFAcuteIPWOClaimID As A
UNION ALL
SELECT a.MemID,
a.FULLNAME,
a.AcuteIPEndDate As DateValue
FROM @NFAcuteIPWOClaimID As A
),
cteOrdered AS
(
SELECT ROW_NUMBER() OVER(ORDER BY DateValue) As RN, *
FROM cteData
)
SELECT oStart.RN, oEnd.RN, oStart.MemID, oStart.FULLNAME, oStart.DateValue As NFAdmitDate, oEnd.DateValue As NFEndDate
FROM cteOrdered As oStart
JOIN cteOrdered As oEnd
ON oStart.RN + 1 = oEnd.RN
AND oEnd.RN % 2 = 0