您可以使用recursive common expression
。下面是一个例子。请注意,您可以更改DATEADD
与其他部分(例如,如果您愿意,可以添加 90 天)- 这是业务逻辑问题。
DECLARE @DataSource TABLE
(
[client_id] INT
,[adm_date] DATE
,[disch_date] DATE
);
INSERT INTO @DataSource ([client_id], [adm_date], [disch_date])
VALUES (1002, '3/11/2005 ', '5/2/2005')
,(1002, '8/30/2005 ', '2/16/2007')
,(1002, '3/16/2017 ', NULL);
WITH DataSource AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [client_id]) AS [row_id]
,[client_id]
,[adm_date]
,DATEADD(MONTH, 3, [adm_date]) AS [3Month Date]
,ISNULL([disch_date], GETUTCDATE()) AS [disch_date]
FROM @DataSource
WHERE DATEADD(MONTH, 3, [adm_date]) <= ISNULL([disch_date], GETUTCDATE())
),
RecursiveDataSource AS
(
SELECT [row_id]
,[client_id]
,[adm_date]
,[3Month Date]
,[disch_date]
,0 AS [level]
FROM DataSource
UNION ALL
SELECT DS.[row_id]
,DS.[client_id]
,DS.[adm_date]
,DATEADD(MONTH, 3, RDS.[3Month Date])
,DS.[disch_date]
,[level] + 1
FROM RecursiveDataSource RDS
INNER JOIN DataSource DS
ON RDS.[row_id] = DS.[row_id]
AND DATEADD(MONTH, 3, RDS.[3Month Date]) < DS.[disch_date]
)
SELECT *
FROM RecursiveDataSource
ORDER BY [row_id]
,[level];