Search code examples
sqlsql-servercasessmsdateadd

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

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?

Solution

  • 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'