0

I have a table in the format given in the below image as Database Table. enter image description here From that table i want to generate a report. The expected format is given. Help me out with the query.

Selvi Moies
  • 75
  • 8
  • 1
    Guess you should show what you have tried or the downvotes will be coming, but the description of what you want is quite good. – maraca Jul 31 '15 at 09:07
  • 1
    Which database it is MYSQL/SQL Server/Oracle? – Deepshikha Jul 31 '15 at 09:13
  • Can there be only 2 types and data in given format only? – Deepshikha Jul 31 '15 at 09:29
  • Hint: in oracle you could do a select from dual with 4 sub selects, once for each type and then union ... easy but maybe not the best way. – maraca Jul 31 '15 at 09:30
  • @maraca: my query is bit lengthy and need to replace the filed names as they are sensitive. But will change col names and post the query soon. – Selvi Moies Jul 31 '15 at 09:40

1 Answers1

2

Assuming there can be only 2 Types in the table in T-SQLyou can write a query as:

select PaymentType,
[Exists only in old],
diff,
[Exists only in New],
([Exists only in old]+diff+[Exists only in New]) as Total
from
(
-- Fetch Data for Type 1
select 'Type1' as PaymentType,
       sum( case when EXIST_IN_New = 'Not Exists' and EXIST_IN_Old='Exists' 
            then Old_Type_1
            else 0 end) as 'Exists only in old',
       sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Not Exists' 
            then New_Type_1
            else 0 end) as 'Exists only in New',
       sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Exists' 
            then Old_Type_1
            else 0 end) - 
         sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Exists' 
            then New_Type_1
            else 0 end)   as diff                  
from 
Test
union
-- Fetch Data for Type 2
select 'Type2' as PaymentType,
       sum( case when EXIST_IN_New = 'Not Exists' and EXIST_IN_Old='Exists' 
            then Old_Type_2
            else 0 end) ,
       sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Not Exists' 
            then New_Type_2
            else 0 end) ,
       sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Exists' 
            then Old_Type_2
            else 0 end) - 
         sum( case when EXIST_IN_New = 'Exists' and EXIST_IN_Old='Exists' 
            then New_Type_2
            else 0 end)                   
from 
Test
)as T

DEMO

Deepshikha
  • 9,896
  • 2
  • 21
  • 21