可以在T-SQL语句中嵌套使用CASE以及DATEADD()函数吗?

huangapple go评论79阅读模式
英文:

Can you use CASE inside another CASE and DATEADD() function in a T-SQL statement?

问题

I have created a query that will report tools and equipment with their required maintenance status.

Here is your current code:

SELECT 
    MS.MaintCompletionDate, RF.ObjectType, RD.ResourceName, 
    MRB.MaintenanceReqName, MS.NextDateDue,
    CASE
    	WHEN MS.NextDateDue < GETDATE() THEN 'PAST DUE'
    	WHEN MS.NextDateDue IS NULL THEN 'N/A'
    	ELSE 'READY'
    END AS MaintenanceStatus
FROM
    CamstarSch.ResourceDef RD
INNER JOIN 
    CamstarSch.MaintenanceStatus MS ON RD.ResourceId = MS.ResourceId
INNER JOIN 
    CamstarSch.ResourceFamily RF ON RF.ResourceFamilyId = RD.ResourceFamilyId
INNER JOIN 
    CamstarSch.AssignedMaintReq AMR ON AMR.AssignedMaintReqId = MS.AssignedMaintReqId
INNER JOIN 
    CamstarSch.MaintenanceReq MR ON AMR.MaintenanceReqId = MR.MaintenanceReqId
INNER JOIN 
    CamstarSch.MaintenanceReqBase MRB ON MRB.MaintenanceReqBaseId = MR.MaintenanceReqBaseId
ORDER BY 
    NextDateDue DESC;

You'd like to set up your statement using the DATEADD function for different maintenance frequencies. Here's an example for 'Bi-Annual':

   WHEN DATEADD(MONTH, 6, MS.LastDateDue) < GETDATE() THEN 'PAST DUE'

You can create similar clauses for other maintenance frequencies. This approach should work for your requirements.

英文:

I am creating a query that will report tools and equipment with their required maintenance status.

I have a column that is currently using the NextDateDue column and checking it vs. the current date to see if it's past due. This column is not very reliable after looking at the data so I want to go by the LastDateDue (Last Maintenance Completion Date) look at the frequency of maintenance (eg. Bi-annual, weekly, monthly), add that frequency to the LastDateDue and check that vs. the current date to determine Past Due or Ready.

Here is my current code:

SELECT 
    MS.MaintCompletionDate, RF.ObjectType, RD.ResourceName, 
    MRB.MaintenanceReqName, MS.NextDateDue,
    CASE
    	WHEN MS.NextDateDue &lt; GETDATE() THEN &#39;PAST DUE&#39;
    	WHEN MS.NextDateDue IS NULL THEN &#39;N/A&#39;
    	ELSE &#39;READY&#39;
    END AS MaintenanceStatus
FROM
    CamstarSch.ResourceDef RD
INNER JOIN 
    CamstarSch.MaintenanceStatus MS ON RD.ResourceId = MS.ResourceId
INNER JOIN 
    CamstarSch.ResourceFamily RF ON RF.ResourceFamilyId = RD.ResourceFamilyId
INNER JOIN 
    CamstarSch.AssignedMaintReq AMR ON AMR.AssignedMaintReqId = MS.AssignedMaintReqId
INNER JOIN 
    CamstarSch.MaintenanceReq MR ON AMR.MaintenanceReqId = MR.MaintenanceReqId
INNER JOIN 
    CamstarSch.MaintenanceReqBase MRB ON MRB.MaintenanceReqBaseId = MR.MaintenanceReqBaseId
ORDER BY 
    NextDateDue DESC;

I'd like to set up my statement in a way that I THINK would look something like:

   WHEN DATEADD(CASE WHEN MRB.MaintenanceReqName IS &#39;Bi-Annual&#39; THEN &#39;month, 6,&#39; MS.LastDateDue) &lt; GETDATE() THEN &#39;PAST DUE&#39;

with more WHEN clauses for each type of frequency.

Is this possible? Am I going about it the wrong way?

答案1

得分: 1

根据我理解你的问题,以下是你想要的内容:

当
  CASE MRB.MaintenanceReqName
    当 '半年度' THEN DATEADD(month, 6, MS.LastDateDue)
    当 '每月' THEN DATEADD(month, 1, MS.LastDateDue)
    当 '每周' THEN DATEADD(week, 1, MS.LastDateDue)
  结束 < GETDATE()
则 '已过期'
英文:

From what I understood of your question this is what you want:

WHEN
  CASE MRB.MaintenanceReqName 
    WHEN &#39;Bi-Annual&#39; THEN DATEADD(month, 6, MS.LastDateDue) 
    WHEN &#39;Monthly&#39; THEN DATEADD(month, 1, MS.LastDateDue) 
    WHEN &#39;Weekly&#39; THEN DATEADD(week, 1, MS.LastDateDue)  
  END &lt; GETDATE() 
THEN &#39;PAST DUE&#39;

答案2

得分: 0

你可以将一个 case 放在另一个 case 内。或者,你可以将多个子句放在外部 case 中。或者,如果你有一些需要重复执行的操作(例如,根据维护频率确定到期日期),可以创建一个函数。选择适合你的数据并增加可维护性的版本。我认为,根据上面的示例,函数可能是最佳选择。

函数:

CREATE FUNCTION dbo.GetDueDate (@MaintenanceReqName varchar(50), @LastDateDue date)  
RETURNS date  
AS  
BEGIN
 DECLARE @NextDueDate date;
 SELECT @NextDueDate = CASE
    WHEN MRB.MaintenanceReqName = 'Quarterly' THEN DATEADD(month, 3, @LastDateDue)    
    WHEN MRB.MaintenanceReqName = 'Bi-Annual' THEN DATEADD(month, 6, @LastDateDue)
    WHEN MRB.MaintenanceReqName = 'Annual' THEN DATEADD(year, 1, @LastDateDue)
    ELSE DATEADD(day, 30, @LastDateDue)
 END
 return @NextDueDate
END
....
 WHEN GetDueDate(MRB.MaintenanceReqName, MS.LastDateDue) < GETDATE() THEN 'PAST DUE'

多个子句:

CASE
    WHEN MS.NextDateDue < GETDATE() AND MRB.MaintenanceReqName = 'Bi-Annual' THEN 'PAST DUE'
    WHEN MS.NextDateDue IS NULL THEN 'N/A'
    ELSE 'READY'
END AS MaintenanceStatus
英文:

You can put one case inside another. Alternatively, you could put multiple clauses into the outside case. Or, if you have something you do repeatedly (determine due date based on Maintenance Frequency, perhaps), make a function. Pick the version that better suits your data and increases maintainability. I think, given the above example, a function might be the best bet.
Function:

CREATE FUNCTION dbo.GetDueDate  (@MaintenanceReqName varchar(50), @LastDateDue date)  
RETURNS date  
AS  
BEGIN
 DECLARE @NextDueDate date;
 SELECT @NextDueDate = CASE
    WHEN MRB.MaintenanceReqName IS &#39;Quarterly&#39; THEN DATEADD(month, 3, @LastDateDue)    
    WHEN MRB.MaintenanceReqName IS &#39;Bi-Annual&#39; THEN DATEADD(month, 6, @LastDateDue)
    WHEN MRB.MaintenanceReqName IS &#39;Annual&#39; THEN DATEADD(year, 1, @LastDateDue)
    ELSE DATEADD(day, 30, @LastDateDue)
 END NextDueDate
 return @NextDueDate
END
....
 WHEN GetDueDate(MRB.MaintenanceReqName,MS.LastDateDue) &lt; GETDATE() THEN &#39;PAST DUE&#39;

Multiple clauses:

CASE
    WHEN MS.NextDateDue &lt; GETDATE() AND MRB.MaintenanceReqName IS &#39;Bi-Annual&#39; THEN &#39;PAST DUE&#39;
    WHEN MS.NextDateDue IS NULL THEN &#39;N/A&#39;
    ELSE &#39;READY&#39;
END AS MaintenanceStatus

huangapple
  • 本文由 发表于 2020年1月3日 23:27:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/59581177.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定