问题描述
我有如下 14K 条记录表(与一个特定 client_id = 1002 相关的数据示例):(我的日期格式是 mm/dd/yyyy，月份在前)
I have 14K records table as the following (example of the data related to one particular client_id = 1002): (my date format is mm/dd/yyyy, months come first)
ClientsEpisodes:
client_id adm_date disch_date
1002 3/11/2005 5/2/2005
1002 8/30/2005 2/16/2007
1002 3/16/2017 NULL
在 SQL Server (T-SQL) 中 - 我需要将 + 3 个月的日期计算到新列 [3Month Date] 中，其中第一个+ 3 个月"是值将根据我现有的 [adm_date] 列计算.然后 + 3 个月应该添加到 [3Months Date] 中的值，然后接下来的 3 个月应该添加到 [3Months Date] 列中的下一个值，依此类推...，直到 [3MonthsDate] <;= [disch_date].当 [3Months Date] 大于 [disch_date] 时，不应填充数据.如果我的 [disch_date] 为 NULL 那么条件应该是[3个月日期] <= 来自 GETDATE() 函数的当前日期(无论是什么).
In SQL Server (T-SQL) - I need to calculate + 3 months date into the new column [3Month Date], where the 1st "+ 3 months" value will be calculated off my existing [adm_date] column. Then + 3 more months should be added to the value in [3Months Date], then the next 3 months should be added to the next value in the [3Months Date] column, and so on..., until [3MonthsDate] <= [disch_date]. When [3Months Date] is more than [disch_date] then the data shouldn't be populated. If my [disch_date] IS NULL then the condition should be [3Months Date] <= current date (whatever it is) from GETDATE() function.
这是我希望看到的结果:(我用不同的颜色突出显示了我的日期偏移量，以便更好地查看)
Here is what I expect to see as a result: (I highlighted my dates offsets with different colors, for a better view)
下面，我将详细解释每个填充(或未填充)的数据集:
Below, I'll clarify with more detailed explanation, about each populated (or not populated) data set:
我在 ClientsEpisode 表中的第一个 [adm_date] 是 2005 年 3 月 11 日.加3个月:3/11/2005 + 3 个月 = 6/11/2005 - 在初始 [disch_date] (5/2/2005) 之后下降 - 未填充
My first [adm_date] from ClientsEpisode table was 3/11/2005. Adding 3 months: 3/11/2005 + 3 months = 6/11/2005 - falls AFTER the initial [disch_date] (5/2/2005) - not populated
Next [adm_date] from ClientEpisode is 8/3/2005 + 3 Months = 11/30/2005;
then + 3 months must be added to 11/30/2005 = 2/30/2006;
then 2/30/2006 + 3 months = 5/30/2006;
then 5/30/2006 + 3 months = 8/30/2006;
then 8/30/2006 + 3 months = 11/30/2006;
then 11/30/2006 + 3 months = 3/2/2007 - falls AFTER my [disch_date]
(2/16/2007) - not populated
相同的算法用于下一个 [adm_date] - [disch_date] 集 11/5/2007-2/7/2009(深蓝色).
the same algorithm for the next [adm_date] - [disch_date] sets 11/5/2007-2/7/2009 (in dark blue).
然后，[adm_date] = 3/16/17，我有 [disch_date] IS NULL，因此，该算法适用直到[3 个月日期] <= 当前日期(在本例中为 10/15/2020)
then, where [adm_date] = 3/16/17, I have [disch_date] IS NULL, so, the algorithm applies until [3 Months Date] <= current date (10/15/2020 in this case)
推荐答案
您可以使用递归通用表达式.下面是一个例子.请注意，您可以将 DATEADD 部分更改为其他部分(例如，如果需要，可以添加 90 天) - 这是业务逻辑的问题.
You can use recursive common expression. Below is an example. Note, that you can change the DATEADD part with other (for example add 90 days if you want) - it's a matter of bussness logic.
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];
这篇关于需要根据 T-SQL 中的“入学日期"列计算出的第一个“3 个月"为列中的每个值添加 3 个月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！