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
Leave feedback about this