mardi 22 décembre 2015

MSSQL Difference from 2 rows

I Have a query and I'm using a PIVOT which return a table in this way

category                date             value

cat1                  2015-08-01         10     
cat1(Previous)        2015-07-01         20
cat2                  2015-09-01         15
cat2(Previous)        2015-08-01         25
cat3                  2015-10-01         NULL
cat3(Previous)        2015-09-01         58

and my code looks like:

select * from (
    select t1.name as name, t1.date as date, t1.value as value from (
        select ct.theName as [name], 
        CONVERT(nvarchar(50),   DATENAME(m, buca.date) 
                               +', ' 
                               + DATENAME(yyyy,buca.date))as date   
        , sum(buca.value) as value from blackU bu
            join blackUCat buc on bu.id = buc.blackucat_id
            join cat_size ct on ct.id = buc.size_id
            join blackU_actual2 buca on buc.id = buca.blackU_Id
        where buca.date between  cast     (@start as VARCHAR(50)) and   cast     (@actual as VARCHAR(50))
         and bu.name =   cast     (@blackU as VARCHAR(50))
            group by buca.date, ct.category_name
            )t1

            union all

            select t2.name as name, t2.date as date, t2.value as value from (
        select ct.theName as [name], 
        CONVERT(nvarchar(50),   DATENAME(m, buca.date) 
                               +', ' 
                               + DATENAME(yyyy,buca.date))as date   
        , sum(buca.value) as value from blackU bu
            join blackUCat buc on bu.id = buc.blackucat_id
            join cat_size ct on ct.id = buc.size_id
            join blackU_actual1 buca on buc.id = buca.blackU_Id
        where buca.date between  cast     (@next as VARCHAR(50)) and   cast     (@end as VARCHAR(50))
         and bu.name =   cast     (@blackU as VARCHAR(50))
            group by buca.date, ct.category_name
            )t2


            union all

            select t3.name as name, t3.date as date, t3.value as value from (
        select ct.theName as [name], 
        CONVERT(nvarchar(50),   DATENAME(m, buca.date) 
                               +', ' 
                               + DATENAME(yyyy,buca.date))as date   
        , sum(buca.value) as value from blackU bu
            join blackUCat buc on bu.id = buc.blackucat_id
            join cat_size ct on ct.id = buc.size_id
            join blackU_actual buca on buc.id = buca.blackU_Id
        where buca.date between  cast     (@start as VARCHAR(50)) and   cast     (@end as VARCHAR(50))
         and bu.name =   cast     (@blackU as VARCHAR(50))
            group by buca.date, ct.category_name
            )t3

            ) as table

All I want is to have a row with name cat1(difference) and to be the difference between first and second row, third and fourth something like this:

category date value

cat1                  2015-08-01         10     
cat1(Previous)        2015-07-01         20
cat1(difference)      2015-09-01        -10    
cat2                  2015-09-01         15
cat2(Previous)        2015-08-01         25
cat2(difference)      2015-08-01        -10 
cat3                  2015-10-01         NULL
cat3(Previous)        2015-09-01         58
cat3(difference)      2015-09-01        -58

Aucun commentaire:

Enregistrer un commentaire