The troubled code:

CASE

WHEN theTime <=0030 THEN '0000'

WHEN theTime >0031 AND theTime <=0130 THEN '0100'

WHEN theTime >0131 AND theTime <=0230 THEN '0200'

WHEN theTime >0231 AND theTime <=0330 THEN '0300'

WHEN theTime >0331 AND theTime <=0430 THEN '0400'

WHEN theTime >0431 AND theTime <=0530 THEN '0500'

WHEN theTime >0531 AND theTime <=0630 THEN '0600'

WHEN theTime >0631 AND theTime <=0730 THEN '0700'

WHEN theTime >0731 AND theTime <=0830 THEN '0800'

WHEN theTime >0831 AND theTime <=0930 THEN '0900'

WHEN theTime >0931 AND theTime <=1030 THEN '1000'

WHEN theTime >1031 AND theTime <=1130 THEN '1100'

WHEN theTime >1131 AND theTime <=1230 THEN '1200'

WHEN theTime >1231 AND theTime <=1330 THEN '1300'

WHEN theTime >1331 AND theTime <=1430 THEN '1400'

WHEN theTime >1431 AND theTime <=1530 THEN '1500'

WHEN theTime >1531 AND theTime <=1630 THEN '1600'

WHEN theTime >1631 AND theTime <=1730 THEN '1700'

WHEN theTime >1731 AND theTime <=1830 THEN '1800'

WHEN theTime >1831 AND theTime <=1930 THEN '1900'

WHEN theTime >1931 AND theTime <=2030 THEN '2000'

WHEN theTime >2031 AND theTime <=2130 THEN '2100'

WHEN theTime >2131 AND theTime <=2230 THEN '2200'

WHEN theTime >2231 AND theTime <=2330 THEN '2300'

WHEN theTime >2331 THEN '0000'

END as GroupedTime

WHEN theTime <=0030 THEN '0000'

WHEN theTime >0031 AND theTime <=0130 THEN '0100'

WHEN theTime >0131 AND theTime <=0230 THEN '0200'

WHEN theTime >0231 AND theTime <=0330 THEN '0300'

WHEN theTime >0331 AND theTime <=0430 THEN '0400'

WHEN theTime >0431 AND theTime <=0530 THEN '0500'

WHEN theTime >0531 AND theTime <=0630 THEN '0600'

WHEN theTime >0631 AND theTime <=0730 THEN '0700'

WHEN theTime >0731 AND theTime <=0830 THEN '0800'

WHEN theTime >0831 AND theTime <=0930 THEN '0900'

WHEN theTime >0931 AND theTime <=1030 THEN '1000'

WHEN theTime >1031 AND theTime <=1130 THEN '1100'

WHEN theTime >1131 AND theTime <=1230 THEN '1200'

WHEN theTime >1231 AND theTime <=1330 THEN '1300'

WHEN theTime >1331 AND theTime <=1430 THEN '1400'

WHEN theTime >1431 AND theTime <=1530 THEN '1500'

WHEN theTime >1531 AND theTime <=1630 THEN '1600'

WHEN theTime >1631 AND theTime <=1730 THEN '1700'

WHEN theTime >1731 AND theTime <=1830 THEN '1800'

WHEN theTime >1831 AND theTime <=1930 THEN '1900'

WHEN theTime >1931 AND theTime <=2030 THEN '2000'

WHEN theTime >2031 AND theTime <=2130 THEN '2100'

WHEN theTime >2131 AND theTime <=2230 THEN '2200'

WHEN theTime >2231 AND theTime <=2330 THEN '2300'

WHEN theTime >2331 THEN '0000'

END as GroupedTime

First of all, the logic excludes every number ending in 31. The result for those will be NULL. Oops.

So here's improvement #1:

GroupedTime =

CASE

WHEN theTime >= 2231 THEN '2300'

WHEN theTime >= 2131 THEN '2200'

WHEN theTime >= 2031 THEN '2100'

WHEN theTime >= 1931 THEN '2000'

WHEN theTime >= 1831 THEN '1900'

WHEN theTime >= 1731 THEN '1800'

WHEN theTime >= 1631 THEN '1700'

WHEN theTime >= 1531 THEN '1600'

WHEN theTime >= 1431 THEN '1500'

WHEN theTime >= 1331 THEN '1400'

WHEN theTime >= 1231 THEN '1300'

WHEN theTime >= 1131 THEN '1200'

WHEN theTime >= 1031 THEN '1100'

WHEN theTime >= 0931 THEN '1000'

WHEN theTime >= 0831 THEN '0900'

WHEN theTime >= 0731 THEN '0800'

WHEN theTime >= 0631 THEN '0700'

WHEN theTime >= 0531 THEN '0600'

WHEN theTime >= 0431 THEN '0500'

WHEN theTime >= 0331 THEN '0400'

WHEN theTime >= 0231 THEN '0300'

WHEN theTime >= 0131 THEN '0200'

WHEN theTime >= 0031 THEN '0100'

ELSE '0000'

END

CASE

WHEN theTime >= 2231 THEN '2300'

WHEN theTime >= 2131 THEN '2200'

WHEN theTime >= 2031 THEN '2100'

WHEN theTime >= 1931 THEN '2000'

WHEN theTime >= 1831 THEN '1900'

WHEN theTime >= 1731 THEN '1800'

WHEN theTime >= 1631 THEN '1700'

WHEN theTime >= 1531 THEN '1600'

WHEN theTime >= 1431 THEN '1500'

WHEN theTime >= 1331 THEN '1400'

WHEN theTime >= 1231 THEN '1300'

WHEN theTime >= 1131 THEN '1200'

WHEN theTime >= 1031 THEN '1100'

WHEN theTime >= 0931 THEN '1000'

WHEN theTime >= 0831 THEN '0900'

WHEN theTime >= 0731 THEN '0800'

WHEN theTime >= 0631 THEN '0700'

WHEN theTime >= 0531 THEN '0600'

WHEN theTime >= 0431 THEN '0500'

WHEN theTime >= 0331 THEN '0400'

WHEN theTime >= 0231 THEN '0300'

WHEN theTime >= 0131 THEN '0200'

WHEN theTime >= 0031 THEN '0100'

ELSE '0000'

END

But here's improvement #2. Now, oddly enough, theTime is a varchar column (it was extracted from a datetime column as varchar for some reason, but I'm working to try to fix that).

Groupedtime = Right('0' + Convert(varchar(2), (Left(theTime, 2) + CASE WHEN Right(theTime, 2) >= 31 THEN 1 ELSE 0 END) % 24), 2) + '00'

More improvements can be made if the theTime column comes in as datetime or a proper numeric such as minutes (instead of the display varchar number where '0100' means 1 o'clock or 60 minutes, not 100 minutes. I will be making that change since the source of the data is also able to be changed.

## No comments:

Post a Comment