Solution to TSQL Beginners Challenge 13

;with MacanieKurczakow as 
(
    select
        Guid    as Kurczak,
        Guid    as KurczakRoboczy,        
        '........-....-....-....-............' as WzrocowyKurczak,
        0 as c1,
        0 as c2,
        0 as c3,
        0 as c4,
        0 as c5,
        0 as ca,
        0 as cb,
        0 as cc,
        0 as cd,
        0 as ce,
        1 as OK,
        substring(Guid,1,1) as KawalekKurczaka
        from
            @t as Kurczaki
        where
            Guid<>''
    union all
    select
        Kurczak,
        substring(KurczakRoboczy,2,len(KurczakRoboczy)-1)   as KurczakRoboczy,
        substring(WzrocowyKurczak,2,len(WzrocowyKurczak)-1) as WzrocowyKurczak,
        c1+
        case 
            when KawalekKurczaka='1' 
            then 1
            else 0
        end,
        c2+
        case 
            when KawalekKurczaka='2' 
            then 1
            else 0
        end,
        c3+
        case 
            when KawalekKurczaka='3' 
            then 1
            else 0
        end,
        c4+
        case 
            when KawalekKurczaka='4' 
            then 1
            else 0
        end,
        c5+
        case 
            when KawalekKurczaka='5' 
            then 1
            else 0
        end,
        ca+
        case 
            when KawalekKurczaka='a' 
            then 1
            else 0
        end,
        cb+
        case 
            when KawalekKurczaka='b' 
            then 1
            else 0
        end,
        cc+
        case 
            when KawalekKurczaka='c' 
            then 1
            else 0
        end,
        cd+
        case 
            when KawalekKurczaka='d' 
            then 1
            else 0
        end,
        ce+
        case 
            when KawalekKurczaka='e' 
            then 1
            else 0
        end,
        case
            when        substring(WzrocowyKurczak,1,1)='-'
                 or    KawalekKurczaka='-' 
             then  
                case
                    when            substring(WzrocowyKurczak,1,1)='-'
                            and     KawalekKurczaka='-' 
                    then 1
                    else 0
                end
             else   OK
        end,
        case
            when len(KurczakRoboczy)>1 
            then substring(KurczakRoboczy,2,1)
            else ''
        end            
        from
            MacanieKurczakow
        where
                    OK=1
            and     KurczaKRoboczy<>''
       
            
),
SwiezeKurczaki as
(
    select 
        Kurczak,
        c1,
        c2,
        c3,
        c4,
        c5,
        ca,
        cb,
        cc,
        cd,
        ce,
        c1+c2+c3+c4+c5+ca+cb+cc+cd+ce as csum,
        row_number() over (partition by OK order by Kurczak asc) as Id        
        from 
            MacanieKurczakow
        where
                OK=1
            and KurczakRoboczy=''
            and c1 >0
            and c2 >0
            and c3 >0
            and c4 >0
            and c5 >0
            and ca >0
            and cb >0
            and cc >0
            and cd >0
            and ce >0    
),
PakowanieKurczakow as
(
    select
        Kurczak ,
        c1,
        c2,
        c3,
        c4,
        c5,
        ca,
        cb,
        cc,
        cd,
        ce,
        csum, 
        Id   
        from
            SwiezeKurczaki
    union all
    select
        'Vertical Sum',
        sum(c1),
        sum(c2),
        sum(c3),
        sum(c4),
        sum(c5),
        sum(ca),
        sum(cb),
        sum(cc),
        sum(cd),
        sum(ce),
        sum(csum),
        max(Id)+1   
        from
            SwiezeKurczaki    
)

select
    Kurczak as 'ValidGuid',
    c1 as '1',
    c2 as '2',
    c3 as '3',
    c4 as '4',
    c5 as '5',
    ca as 'A',
    cb as 'B',
    cc as 'C',
    cd as 'D',
    ce as 'E',
    csum as 'H Sum'
    from
        PakowanieKurczakow
    order by 
        Id


        

Tags: