I have this query used within a view, this is taking horrible amount of time, whe query select top 100 rows, it is taking almost 7 minutes, there are 10 mill rows in this table:StartDTM and ENDDTM both are datetime datatype fields: is this logic the efficient?Please need help: CASE WHEN DATEPART(Hour, StartDTM) = 1 THEN CASE WHEN DATEPART(Hour, EndDTM) = 1 THEN CAST(DATEPART(minute, EndDTM) - DATEPART(minute, StartDTM) AS FLOAT)/60 ELSE CAST(60 - DATEPART(minute, StartDTM) AS FLOAT)/60 END WHEN DATEPART(Hour, EndDTM) = 1 THEN CAST(DATEPART(minute, EndDTM) AS FLOAT)/60 WHEN 1 BETWEEN DATEPART(Hour, StartDTM) AND DATEPART(Hour, EndDTM) THEN 1 ELSE 0 END AS '1AM', CASE WHEN DATEPART(Hour, StartDTM) = 2 THEN CASE WHEN DATEPART(Hour, EndDTM) = 2 THEN CAST(DATEPART(minute, EndDTM) - DATEPART(minute, StartDTM) AS FLOAT)/60 ELSE CAST(60 - DATEPART(minute, StartDTM) AS FLOAT)/60 END WHEN DATEPART(Hour, EndDTM) = 2 THEN CAST(DATEPART(minute, EndDTM) AS FLOAT)/60 WHEN 2 BETWEEN DATEPART(Hour, StartDTM) AND DATEPART(Hour, EndDTM) THEN 1 ELSE 0 END AS '2AM',
I only pasted for 1Am and 2AM hours using datepart.Thank you very much for the helpful info.