Two Column Report Based on Multiples of Integers

Dear Donnie

You want to generate a report, so you need a query that perform as fast as possible. In the other hand, we do not want to use a temp table or table variable, if it's possible. This solution have both.

;with set1 as ( 
    select 0 as number
    union all 
    select 1 
    union all 
    select 2 
    union all 
    select 3 
    union all 
    select 4 
    union all 
    select 5 
    union all 
    select 6 
    union all 
    select 7 
    union all 
    select 8 
    union all 
    select 9 
        ), set2 as (
            select 
                t1.number + t2.number*10 + t3.number*100 as [Column 1]
            from 
                set1 as t1,  
                set1 as t2, 
                set1 as t3
            where 
                ( t1.number + t2.number*10 + t3.number*100 ) <= 102 
            )
select
    [Column 1] , 
    [Column 2] = case 
                    when [Column 1] % 4 = 0 and [Column 1] % 7 = 0 then 'green'
                    when [Column 1] % 4 = 0 then 'blue'
                    when [Column 1] % 7 = 0 then 'yellow'
                    else NULL
                 end
from 
    set2 ;

Regards,

Saeid

---

http://sqldevelop.wordpress.com/