sql-server - 带有 CTE 的案例语句（条件）
问题描述
我有一个我正在尝试转换为 TVF 的功能。但我面临的问题是，该功能是多方的，具有多个 IF 条件。
我能够将一种条件转换为 TVF，为此我使用了 CTE。（我之前从这个论坛获得了帮助）。但是当我试图通过包含条件语句将整个函数转换为 TVF 时，我遇到了麻烦。
CREATE FUNCTION [dbo].[GetEventDecisionDueDate]
(
@EventNumber VARCHAR(20),
@BaseId VARCHAR(25),
@CheckDate BIT
)
RETURNS DateTime
AS
BEGIN
DECLARE @CurrentStatus VARCHAR(20)
DECLARE @BaseDateTime DateTime
DECLARE @DecisionDueDate DateTime
SELECT @CurrentStatus = (SELECT cis.IncidentStatus
FROM CurrentStatus cis
INNER JOIN Base r WITH (NOLOCK) ON (cis.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND r.BaseId = @BaseId)
SELECT @BaseDateTime = (SELECT BaseDateTime FROM Base r
WHERE (r.EventNumber = @EventNumber) AND r.BaseId = @BaseId)
IF @CurrentStatus IN ('0','6') AND @CheckDate = 1
BEGIN
SET @DecisionDueDate = DATEADD(DAY, 30, @BaseDateTime)
WHILE @DecisionDueDate < getdate()
SET @DecisionDueDate = DATEADD(DAY, 30, @DecisionDueDate)
DECLARE @FinalJournalDate DateTime
SELECT @FinalJournalDate = (SELECT TOP 1 ij.Date
FROM EventDetails_Journal ij
INNER JOIN EventJournal p ON ij.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber AND r.BaseId = @BaseId) AND ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC)
IF(DATEADD(DAY, 30, @BaseDateTime) < getdate() AND
(@FinalJournalDate is null OR DATEADD(DAY, 30, @FinalJournalDate) < getdate()) AND
DATEADD(DAY, 14, @DecisionDueDate) > DATEADD(DAY, 30, getdate()))
SET @DecisionDueDate = DATEADD(DAY, -30, @DecisionDueDate)
ELSE IF((@FinalJournalDate is not null ) AND (DATEADD(DAY, 30, @FinalJournalDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(DAY, 30, @DecisionDueDate)
END
IF @CurrentStatus IN ('0','6') AND @CheckDate = 0
BEGIN
SET @DecisionDueDate = DATEADD(DAY, 30, @BaseDateTime)
WHILE @DecisionDueDate < getdate()
SET @DecisionDueDate = DATEADD(DAY, 30, @DecisionDueDate)
DECLARE @Final30DayDecisonDate DateTime
DECLARE @Final6MonthDecisonDate DateTime
DECLARE @Final30DayJournalDate DateTime
SELECT @Final30DayDecisonDate = (SELECT TOP 1 idps.BaseSupplementDate
FROM EventDetails_Supplements idps
INNER JOIN EventDetails p ON idps.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND
r.BaseId = @BaseId AND idps.IsThirtyDayReview = 1 ORDER BY idps.BaseSupplementDate DESC)
SELECT @Final6MonthDecisonDate = (SELECT TOP 1 idps.BaseSupplementDate
FROM EventDetails_Supplements idps
INNER JOIN EventDetails p ON idps.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND
r.BaseId = @BaseId AND idps.IsSixMonthReview = 1 ORDER BY idps.BaseSupplementDate DESC)
SELECT @Final30DayJournalDate = (SELECT TOP 1 ij.Date
FROM EventDetails_Journal ij
INNER JOIN EventJournal p ON ij.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber AND r.BaseId = @BaseId) AND ij.ReviewType = '30-DAY' ORDER BY ij.Date DESC)
IF(DATEADD(DAY, 30, @BaseDateTime) < getdate() AND
(@Final30DayJournalDate is null OR DATEADD(DAY, 30, @Final30DayJournalDate) < getdate()) AND
(@Final30DayDecisonDate is null OR DATEADD(DAY, 30, @Final30DayDecisonDate) < getdate()) AND
(@Final6MonthDecisonDate is null OR DATEADD(DAY, 30, @Final6MonthDecisonDate) < getdate()) AND
DATEADD(DAY, 14, @DecisionDueDate) > DATEADD(DAY, 30, getdate()))
SET @DecisionDueDate = DATEADD(DAY, -30, @DecisionDueDate)
ELSE
BEGIN
IF((@Final30DayJournalDate is not null ) AND (DATEADD(DAY, 30, @Final30DayJournalDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(DAY, 30, @DecisionDueDate)
IF((@Final30DayDecisonDate is not null ) AND (DATEADD(DAY, 30, @Final30DayDecisonDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(DAY, 30, @DecisionDueDate)
IF((@Final6MonthDecisonDate is not null ) AND (DATEADD(DAY, 30, @Final6MonthDecisonDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(MONTH, 6, @DecisionDueDate)
END
END
ELSE IF @CurrentStatus = '4' AND @CheckDate = 0
BEGIN
DECLARE @InactiveBaseSupplementDate DateTime
DECLARE @LastInactiveBaseSupplementDate DateTime
DECLARE @LastInactiveJournalDate DateTime
SELECT @InactiveBaseSupplementDate = (SELECT TOP 1 idps.BaseSupplementDate
FROM EventDetails_Supplements idps
INNER JOIN EventDetails p ON idps.PageId = p.Id
INNER JOIN IncidentDetailsPage_Incident idp ON idps.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND
r.BaseId = @BaseId AND idp.IncidentStatus = '4' ORDER BY idps.BaseSupplementDate ASC)
SET @DecisionDueDate = DATEADD(Month, 6, @InactiveBaseSupplementDate)
WHILE @DecisionDueDate < getdate()
SET @DecisionDueDate = DATEADD(Month, 6, @DecisionDueDate)
SELECT @LastInactiveBaseSupplementDate = (SELECT TOP 1 idps.BaseSupplementDate
FROM EventDetails_Supplements idps
INNER JOIN EventDetails p ON idps.PageId = p.Id
INNER JOIN IncidentDetailsPage_Incident idp ON idps.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND
r.BaseId = @BaseId AND idp.IncidentStatus = '4' AND idps.IsInactiveReview = 1 ORDER BY idps.BaseSupplementDate DESC)
SELECT @LastInactiveJournalDate = (SELECT TOP 1 ij.Date
FROM EventDetails_Journal ij
INNER JOIN EventJournal p ON ij.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND
r.BaseId = @BaseId AND ij.ReviewType = 'Inactive' ORDER BY ij.Date DESC)
IF(DATEADD(MONTH, 6, @InactiveBaseSupplementDate) < getdate() AND
(@LastInactiveJournalDate is null OR DATEADD(MONTH, 6, @LastInactiveBaseSupplementDate) < getdate()) AND
(@LastInactiveBaseSupplementDate is null OR DATEADD(MONTH, 6, @LastInactiveJournalDate) < getdate()) AND
DATEADD(DAY, 14, @DecisionDueDate) > DATEADD(MONTH, 6, getdate()))
SET @DecisionDueDate = DATEADD(Month, -6, @DecisionDueDate)
ELSE
BEGIN
if((@LastInactiveBaseSupplementDate is not null ) AND (DATEADD(Month, 6, @LastInactiveBaseSupplementDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(Month, 6, @DecisionDueDate)
if((@LastInactiveJournalDate is not null ) AND (DATEADD(Month, 6, @LastInactiveJournalDate) >= @DecisionDueDate))
SET @DecisionDueDate = DATEADD(Month, 6, @DecisionDueDate)
END
END
END
GO
如您所见，我有多个 if 条件，并且我能够转换一个 if 条件，如下所示。我用条件转换了第一部分
IF @CurrentStatus IN ('0','6') AND @CheckDate = 1
CREATE FUNCTION [dbo].[GetEventDecisionDueDate_test1]
(
@EventNumber VARCHAR(20),
@AgencyOri VARCHAR(25),
@CheckDate BIT
)
RETURNS TABLE
AS
RETURN
WITH X
AS (SELECT cis.IncidentStatus AS CurrentIncidentStatus,
r.BaseDateTime,
FROM CurrentIncidentStatus cis
INNER JOIN Base r WITH (NOLOCK) ON (cis.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber) AND r.AgencyOri = @AgencyOri
AND cis.IncidentStatus IN ('0','6')
AND @CheckDate = 1)
SELECT X.CurrentIncidentStatus,
X.BaseDateTime,
CA4.DecisionDueDate
FROM X
CROSS APPLY (VALUES(DATEADD(DAY, 30, X.BaseDateTime))) CA1(DecisionDueDate)
CROSS APPLY (VALUES(DATEADD(DAY, 30 * CEILING(( IIF(CAST(GETDATE() AS TIME) > CAST(CA1.DecisionDueDate AS TIME), 1, 0)
+ DATEDIFF(DAY, CA1.DecisionDueDate, GETDATE()) ) / 30.0), CA1.DecisionDueDate))) CA2(DecisionDueDate)
CROSS APPLY (SELECT TOP 1 ij.Date
FROM EventDetails_Journal ij
INNER JOIN EventJournal p ON ij.PageId = p.Id
INNER JOIN Journal f ON p.JournalId = f.Id
INNER JOIN Base r WITH (NOLOCK) ON (f.BaseId = r.Id)
WHERE (r.EventNumber = @EventNumber AND r.AgencyOri = @AgencyOri) AND
ij.ReviewType = 'Supervisor Monthly Review' ORDER BY ij.Date DESC) CA3(IncidentJournalDate)
CROSS APPLY (VALUES ( CASE
WHEN ( DATEADD(DAY, 30, X.BaseDateTime) < GETDATE()
AND ( CA3.IncidentJournalDate IS NULL
OR DATEADD(DAY, 30, CA3.IncidentJournalDate) < GETDATE() )
AND DATEADD(DAY, 14, CA2.DecisionDueDate) > DATEADD(DAY, 30, GETDATE()) )
THEN DATEADD(DAY, -30, CA2.DecisionDueDate)
WHEN( ( CA3.IncidentJournalDate IS NOT NULL )
AND ( DATEADD(DAY, 30, CA3.IncidentJournalDate) >= CA2.DecisionDueDate ) )
THEN DATEADD(DAY, 30, CA2.DecisionDueDate)
ELSE CA2.DecisionDueDate
END )) CA4(DecisionDueDate);
GO
我会尽量让它简单。如果有人可以帮助我，将所有条件逻辑包含在一个有用的函数中。
解决方案
您可以使用以下方法对第二部分执行相同的转换UNION ALL：
SELECT *
FROM (-- all nested logic here
)
WHERE @CurrentStatus IN ('0','6') AND @CheckDate = 1
UNION ALL
SELECT *
FROM (-- rest of nested logic here
)
WHERE @CurrentStatus = '4' AND @CheckDate = 0
推荐阅读
- php - 如何计算两个不同年份的日期的总分钟数？
- android - Android 9 (Pie)，Context.startForegroundService() 没有调用 Service.startForeground()：ServiceRecord
- python - 烧瓶变量不转移到下一页
- vue.js - 尝试通过控制台设置变量时未定义应用程序
- opencv - opencv中的色相值范围
- sqlite - Delphi Firedac 无法识别 sqlite3 中的新 ALTER 功能
- php - Symfony | 如何从控制器中的存储库访问自定义方法？
- python - 在两个字典中循环以在 python 中创建一个新字典
- javascript - 随机排序的图像插件
- reactjs - 按钮 onSubmit 未定义状态