How can I simplify complicated nested case expressions?

I have the below code, which I know is ridiculous:

Select *    INTO #detailsstudents 
FROM (     
    SELECT DISTINCT     
        CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent               
            ELSE  cv.ConcernCount END AS Rating
      , case WHEN vr.Holidaydate IS NULL THEN  '2100-01-01'                
             ELSE CASE   WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 5, vr.Holidaydate) 
                         WHEN cv.ConcernCount="High" THEN DATEADD(day, 10,vr.Holidaydate)
                         WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 15, vr.Holidaydate)
                         WHEN cv.ConcernCount="Low" THEN DATEADD(day, 20, vr.Holidaydate)
                END
            END AS Presentstudent,
        CASE WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                        ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 5, vr.Holidaydate)
                                  WHEN cv.ConcernCount="High" THEN DATEADD(day, 10, vr.Holidaydate)
                                  WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 15, vr.Holidaydate)
                                  WHEN cv.ConcernCount="Low" THEN DATEADD(day, 20, vr.Holidaydate)
                                  END
                        END) IS NOT NULL 
                    AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                              ELSE cv.ConcernCount
                               END) = 'High' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 45 AS INT)
            WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                        ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                  WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                  WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                  WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                  END
                        END) IS NOT NULL 
                 AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                           ELSE cv.ConcernCount
                            END) = 'Critical' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 15 AS INT)
            WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                      ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                               END
                       END) IS NOT NULL 
                AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                          ELSE cv.ConcernCount
                          END) = 'Medium' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 90 AS INT)
            WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                       ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                 WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                 WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                 WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                 END
                      END) IS NOT NULL
                AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                          ELSE cv.ConcernCount
                          END) = 'Low' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 180 AS INT)
            ELSE 0  
            END AS Leaves,
        -- Additional column based on PresentstudentSLA and Rating 
        ISNULL(IIF( CASE WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                                    ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                              WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                              WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                              WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                              END
                                   END) IS NOT NULL
                              AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                                        ELSE cv.ConcernCount
                                        END) = 'High' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 45 AS INT)
                          WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                                     ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                               WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                               WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                               WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                               END
                                     END) IS NOT NULL
                              AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                                        ELSE cv.ConcernCount
                                        END) = 'Critical' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 15 AS INT)
                          WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                                     ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                               WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                               WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                               WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                               END
                                     END) IS NOT NULL
                              AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                                        ELSE cv.ConcernCount
                                        END) = 'Medium' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 90 AS INT)
                           WHEN (CASE WHEN vr.Holidaydate IS NULL THEN '2100-01-01'
                                      ELSE CASE WHEN cv.ConcernCount="Critical" THEN DATEADD(day, 15, vr.Holidaydate)
                                                WHEN cv.ConcernCount="High" THEN DATEADD(day, 45, vr.Holidaydate)
                                                WHEN cv.ConcernCount="Medium" THEN DATEADD(day, 90, vr.Holidaydate)
                                                WHEN cv.ConcernCount="Low" THEN DATEADD(day, 180, vr.Holidaydate)
                                                END
                                      END) IS NOT NULL
                                AND (CASE WHEN dcve.Presentstudent IS NOT NULL THEN dcve.Presentstudent
                                          ELSE cv.ConcernCount
                                          END) = 'Low' THEN CAST(DATEDIFF(day, VR.Holidaydate, @CurrentDate) - 180 AS INT)
                           ELSE 0     
                           END \<= 0,     1,     0 ), 0) AS AbsentStudent  
    FROM dbo.student cv
    LEFT JOIN dimDiv dcve ON dcve.CVE = cv.name
        AND dcve.isActive = 1
    LEFT JOIN dbo.RatingDetailsstudent ON Vr.CVE = dcve.CVE                    
) Z                                                    
WHERE rn = 1;   

INSERT INTO dbo.studentmastertable  (
    LoadDate,
    Rating,
    PresentstudentSLA,
    Leaves,
    AbsentStuden,
) 
SELECT DISTINCT @CurrentDate  AS LoadDate,
    fd.Rating,
    fd.PresentstudentSLA ,
    TRY_CONVERT(INT, fd.Leaves) AS Leaves,
    TRY_CONVERT(INT, fd.AbsentStuden) AS AbsentStudent,
    fd.epssConcernCount AS EPSSConcernCount,
FROM #detailsstudents fd
LEFT JOIN dbo.DimApplicationDetails a ON fd.ApplicationCode = a.ApplicationCode
    AND a.isActive = 1
    

How can I refactor this to reduce the nested CASE expressions? Will a temp table help?

You need to sign in to view this answers