SQL Quick and Easy Tips - SQL Saturday San Diego

Here is the slide deck for two of my SQL Saturday presentations. Enjoy!

Quick & Easy SQL Tips

View more presentations from Ike Ellis

Create a Numbers Table with Buckets Using a Recursive CTE

I needed a numbers table from –400 to 5000. I needed every number to be in a specific bucket for a report. For instance number 55 would be in the bucket "31 – 60".

This code elegantly creates the data for the number ranges (without listing each of them) and for those buckets.


with myNumberCounts as
(
select CAST(-400 as int) NumValue
union all
select NumValue + 1
from mynumberCounts
where numvalue + 1 <= 5000
)
select NumValue
, case
when NumValue <= 30 then '0 - 30'
when NumValue >30 and NumValue <= 60 then '31 - 60'
when NumValue > 60 and NumValue <= 90 then '61 - 90'
when NumValue > 90 and NumValue <= 120 then '91 - 120'
when NumValue > 120 and NumValue <= 150 then '121 - 150'
when NumValue > 150 and NumValue <= 180 then '151 - 180'
when NumValue > 180 and NumValue <= 365 then '181 - 365'
else '365+'
end AS DayBucket
from myNumberCounts
option (maxRecursion 0)