Resultset split up help needed

Transact-SQL
1

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'

Expected output: by comparing the two two set with MEMiID and AcuteIPAdmitDate falls between NFAdmitDate and NFEndDate the below resultset should be shown:

MemID FULLNAME NFAdmitDate NFEndDate
memA SMIT 2015-02-03 00:00:00.000 2015-06-23 00:00:00.000
memA SMIT 2015-06-26 00:00:00.000 2015-07-29 00:00:00.000
memA SMIT 2015-07-31 00:00:00.000 2015-07-31 00:00:00.000

can any one help on this?

2

hi friends,
any update for me?

3

sorry not understanding the logic, can you please explain??

4

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