TSQL Urgent help needed

Transact-SQL
1
Query tried:

`Declare @Test TABLE(MemID VARCHAR(100),FULLNAME VARCHAR(100),NFAdmitDate datetime,NFEndDate datetime)
insert into @Test
SELECT 'memA','SMIT','2015-01-01 00:00:00.000','2015-04-07 00:00:00.000' UNION ALL
SELECT 'memA','SMIT','2015-04-21 00:00:00.000','2015-05-25 00:00:00.000' UNION ALL
SELECT 'memA','SMIT','2015-05-26 00:00:00.000','2015-05-27 00:00:00.000' UNION ALL
SELECT 'memA','SMIT','2015-06-30 00:00:00.000','2015-07-01 00:00:00.000' UNION ALL
SELECT 'memA','SMIT','2015-07-13 00:00:00.000','2015-07-31 00:00:00.000' UNION ALL
SELECT 'memA','SMIT','2015-08-14 00:00:00.000','2015-08-30 00:00:00.000'
;WITH CTE_NFLIST1 AS (
SELECT ROW_NUMBER() OVER (PARTITION BY Memid ORDER BY NFAdmitDate desc)as Rwno,*
FROM @Test
),
CTE_NFLIST2 AS (
SELECT A.MemID,A.Fullname,A.NFAdmitDate,A.NFEndDate,isnull(DateDiff(dd,B.NFEndDate,A.NFAdmitDate)+1,0) AS DIF
FROM CTE_NFLIST1 A left JOIN CTE_NFLIST1 B on A.memid=b.memid and A.Rwno+1 =B.Rwno
)
SELECT * FROM CTE_NFLIST2
order by NFAdmitDate

Expected output: MemID Fullname NFAdmitDate NFEndDate NFStayDays Episode memA SMIT 2015-01-01 00:00:00.000 2015-04-07 00:00:00.000 0 1 memA SMIT 2015-04-21 00:00:00.000 2015-05-25 00:00:00.000 15 1 memA SMIT 2015-05-26 00:00:00.000 2015-05-27 00:00:00.000 2 1 memA SMIT 2015-06-30 00:00:00.000 2015-07-01 00:00:00.000 35 2 memA SMIT 2015-07-13 00:00:00.000 2015-07-31 00:00:00.000 13 2 memA SMIT 2015-08-14 00:00:00.000 2015-08-30 00:00:00.000 15 2 NFStay days calculated based on Previous day NFEndDate and Next Day NFAdmitDate

whenever NFStaydays >30 days,Episode days should be incremented to 1,2,3.. based on order by NFAdmitDate. in clear, whenever gap between first NFEnddate and next NFAdmitDate >30 i need to increment the number.

2

Hi ,

Try this:

SELECT 
		* 		
		,CA.Episodes + 1 AS Episodes
	FROM CTE_NFLIST2 AS A
	CROSS APPLY
	(
		SELECT COUNT(*) AS Episodes
		FROM
			CTE_NFLIST2 AS CA_2
		WHERE
			A.NFAdmitDate>=CA_2.NFAdmitDate
			AND CA_2.DIF >30
	)CA
	order by NFAdmitDate

I added the CROSS APPLY to your final select

MemID	Fullname	NFAdmitDate	NFEndDate	DIF	Episodes
memA	SMIT	2015-01-01 00:00:00.000	2015-04-07 00:00:00.000	0	1
memA	SMIT	2015-04-21 00:00:00.000	2015-05-25 00:00:00.000	15	1
memA	SMIT	2015-05-26 00:00:00.000	2015-05-27 00:00:00.000	2	1
memA	SMIT	2015-06-30 00:00:00.000	2015-07-01 00:00:00.000	35	2
memA	SMIT	2015-07-13 00:00:00.000	2015-07-31 00:00:00.000	13	2
memA	SMIT	2015-08-14 00:00:00.000	2015-08-30 00:00:00.000	15	2
1 Like
3

Thanks Stepson .this is what i expected.Appreciate your help at right time

4

You are welcome!

1 Like