英文:
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 < 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;
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 'Bi-Annual' THEN 'month, 6,' MS.LastDateDue) < GETDATE() THEN 'PAST DUE'
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 'Bi-Annual' THEN DATEADD(month, 6, MS.LastDateDue)
WHEN 'Monthly' THEN DATEADD(month, 1, MS.LastDateDue)
WHEN 'Weekly' THEN DATEADD(week, 1, MS.LastDateDue)
END < GETDATE()
THEN 'PAST DUE'
答案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 'Quarterly' THEN DATEADD(month, 3, @LastDateDue)
WHEN MRB.MaintenanceReqName IS 'Bi-Annual' THEN DATEADD(month, 6, @LastDateDue)
WHEN MRB.MaintenanceReqName IS 'Annual' THEN DATEADD(year, 1, @LastDateDue)
ELSE DATEADD(day, 30, @LastDateDue)
END NextDueDate
return @NextDueDate
END
....
WHEN GetDueDate(MRB.MaintenanceReqName,MS.LastDateDue) < GETDATE() THEN 'PAST DUE'
Multiple clauses:
CASE
WHEN MS.NextDateDue < GETDATE() AND MRB.MaintenanceReqName IS 'Bi-Annual' THEN 'PAST DUE'
WHEN MS.NextDateDue IS NULL THEN 'N/A'
ELSE 'READY'
END AS MaintenanceStatus
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论