[SQL] please help me to optimized the below query where i should be use temp table instead on...

Select * INTO #detailsstudents
FROM ( SELECT DISTINCT
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 AbsentStuden 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 AbsentStuden, fd.epssConcernCount AS EPSSConcernCount, FROM #detailsstudents fd LEFT JOIN dbo.DimApplicationDetails a ON fd.ApplicationCode = a.ApplicationCode AND a.isActive = 1

Continue reading...