Simplify Cumbersome Code

Transact-SQL
1

Hi There, please help to simplify this cumbersome code:
Select CONVERT(varchar(10), xh.H_DateTime, 111) As 'Date'
, DATENAME(WEEKDAY, CONVERT(varchar(10), xh.H_DateTime, 111)) As 'Day Of Week'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) < 8 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) < 8 Then 1 Else 0 End) End End) As 'Before 08'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 8 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 8 Then 1 Else 0 End) End End) As '08-09'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 9 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 9 Then 1 Else 0 End) End End) As '09-10'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 10 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 10 Then 1 Else 0 End) End End) As '10-11'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 11 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 11 Then 1 Else 0 End) End End) As '11-12'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 12 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 12 Then 1 Else 0 End) End End) As '12-13'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 13 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 13 Then 1 Else 0 End) End End) As '13-14'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 14 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 14 Then 1 Else 0 End) End End) As '14-15'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 15 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 15 Then 1 Else 0 End) End End) As '14-16'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 16 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 16 Then 1 Else 0 End) End End) As '16-17'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 17 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 17 Then 1 Else 0 End) End End) As '17-18'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) = 18 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) = 18 Then 1 Else 0 End) End End) As '18-19'
, Sum(Case When Isnull(XHB.PTPBroken,'') = '' Then (Case When DATEPART(HOUR, xh.H_DateTime) >= 19 Then 1 Else 0 End) Else Case When Isnull(XHB.PTPBroken,'') >= Isnull(XOC.OwnerChanged,'') Then 0 Else (Case When DATEPART(HOUR, xh.H_DateTime) >= 19 Then 1 Else 0 End) End End) As 'After 19'

2

I haven't tested this with sample data, and I'm guessing at the JOIN conditions, but the general idea should work:

;WITH cte AS (SELECT CAST(xh.H_DateTime AS date) TheDate
,DATEPART(HOUR, xh.H_DateTime) TheHour
,CASE 
WHEN XHB.PTPBroken IS NULL THEN 1
WHEN XHB.PTPBroken='' THEN 1
WHEN XHB.PTPBroken<XOC.OwnerChanged THEN 1
ELSE 0 END AS Broken
FROM XHB 
INNER JOIN XH ON XHB.XH_ID=XH.ID
INNER JOIN XOC ON XOC.XH_ID=XH.ID)
SELECT CONVERT(VARCHAR(10),TheDate,111) AS [Date]
,DATENAME(WEEKDAY,TheDate) AS [Day Of Week]
,SUM(CASE WHEN TheHour<8 THEN Broken END) AS [Before 08]
,SUM(CASE WHEN TheHour=8 THEN Broken END) AS [08-09]
,SUM(CASE WHEN TheHour=9 THEN Broken END) AS [09-10]
,SUM(CASE WHEN TheHour=10 THEN Broken END) AS [10-11]
,SUM(CASE WHEN TheHour=11 THEN Broken END) AS [11-12]
,SUM(CASE WHEN TheHour=12 THEN Broken END) AS [12-13]
,SUM(CASE WHEN TheHour=13 THEN Broken END) AS [13-14]
,SUM(CASE WHEN TheHour=14 THEN Broken END) AS [14-15]
,SUM(CASE WHEN TheHour=15 THEN Broken END) AS [15-16]
,SUM(CASE WHEN TheHour=16 THEN Broken END) AS [16-17]
,SUM(CASE WHEN TheHour=17 THEN Broken END) AS [17-18]
,SUM(CASE WHEN TheHour=18 THEN Broken END) AS [18-19]
,SUM(CASE WHEN TheHour>=19 THEN Broken END) AS [After 19]
FROM cte
GROUP BY TheDate

You'll need to test it with your own data to ensure it returns accurate results.

For future reference, if you're repeating blocks of CASE expressions in the same line, rewrite them to move them to the outermost expression, and enclose the other expressions. The CASE expressions for the Hour date part are actually the last thing that need to be calculated; your original lines were basically inside-out.

Generally, you want to avoid nesting CASE expressions as well, I used a Common Table Expression (CTE) to apply the CASE logic once to determine if something is Broken or not. Once that's done, the SUM is very simple, and CASE is only needed to pivot the hours into separate columns.

It's also a better practice to avoid wrapping columns in ISNULL() and then comparing them to another ISNULL() expression. The CASE expression I used simplifies the test, and handles the NULL case immediately; subsequent comparisons no longer require ISNULL() or COALESCE() to handle nulls.

3

Hi Robert, as usual your code worked perfectly. My mind does not work like that. I did not know that using the cte is like using a #temp file and then you use the #temp file in the select statement. TThis is something new for me thanks. The way you broke the statement down into two parts simplifies the script a lot. Thanks again.