Recently we had a little tricky problem to solve. We were displaying a report in which we had to bucket numbers in a range, such that only consecutive numbers should be in that range, if any break is there, then a new range should start. Our first solution did not worked as required. Most difficult part was identifying numbers in a sequence, and placing them in a bucket. We could not create any simple T-SQL queries which could easily sort these things out. Then we thought of first capturing the bucket of each number so that we can easily work it out, and that was not possible without cursors. Lets have a look how we did that.

Continue reading →

Recently I saw a puzzle on SQL Server Central, I was intrigued to solve it myself, so here is what I was able to accomplish, and in less than 10 minutes.

DECLARE @i int
SET @i = 0

CREATE TABLE #X (val INT)

WHILE @i < 100
BEGIN
    SELECT @i = @i + 1
    INSERT INTO #X VALUES (@i)
END

SELECT val,
       val % 3,
       val % 5,
       CASE 
            WHEN val % 3 = 0 AND val % 5 = 0 THEN 'BIZZBUZZ'
            WHEN val % 3 = 0 THEN 'BIZZ'
            WHEN val % 5 = 0 THEN 'BUZZ'
       END xval
FROM   #X

DROP TABLE #X