I need to differentiate the data from Query1 and query2 for reporting purpose:
Only difference is case statement between both queries.
Present approach:-
Currently I am using union operator and using flag concept to differentiate both the queries.
Is there any way where we can write in single sql statement and I am fine with one more static table also.
Query1:
One view of data:-
select
year_qtr,
CASE
WHEN REV.DIVISION IN ('80','A8','R8')
THEN 'G1'
WHEN REV.DIVISION='D5'
THEN 'G2'
WHEN REV.DIVISION IN ('A5','K8','K9','TQ','RQ','R9')
THEN 'G3'
WHEN REV.DIVISION IN('H5','H8')
THEN 'G4'
WHEN REV.DIVISION='Z8'
THEN 'G5'
WHEN REV.DIVISION IN ('45','63','7H','Z1','7Y')
THEN 'G6'
END AS BRAND,
round(sum(USD_PLAN_GL_POSTD_AMT) * .000001,6)as USD_PLAN_GL_POSTD_AMT
from table1 rev
group by
year_qtr,
CASE
WHEN REV.DIVISION IN ('80','A8','R8')
THEN 'G1'
WHEN REV.DIVISION='D5'
THEN 'G2'
WHEN REV.DIVISION IN ('A5','K8','K9','TQ','RQ','R9')
THEN 'G3'
WHEN REV.DIVISION IN('H5','H8')
THEN 'G4'
WHEN REV.DIVISION='Z8'
THEN 'G5'
WHEN REV.DIVISION IN ('45','63','7H','Z1','7Y')
THEN 'G6'
END
Query2:
Another view of data:-
select
year_qtr,
CASE
WHEN REV.DIVISION IN ('80','A8','R8','R1')
THEN 'G1'
WHEN REV.DIVISION in('D6','D7')
THEN 'G2'
WHEN REV.DIVISION IN ('A5','K8','K9','TQ','RQ','R8','R7')
THEN 'G3'
WHEN REV.DIVISION IN('H3','H9')
THEN 'G4'
WHEN REV.DIVISION='Z8'
THEN 'G5'
WHEN REV.DIVISION IN ('45','63','7I','Z6')
THEN 'G6'
END AS BRAND,
round(sum(USD_PLAN_GL_POSTD_AMT) * .000001,6)as USD_PLAN_GL_POSTD_AMT
from table1 rev
group by
year_qtr,
CASE
WHEN REV.DIVISION IN ('80','A8','R8','R1')
THEN 'G1'
WHEN REV.DIVISION in('D6','D7')
THEN 'G2'
WHEN REV.DIVISION IN ('A5','K8','K9','TQ','RQ','R8','R7')
THEN 'G3'
WHEN REV.DIVISION IN('H3','H9')
THEN 'G4'
WHEN REV.DIVISION='Z8'
THEN 'G5'
WHEN REV.DIVISION IN ('45','63','7I','Z6')
THEN 'G6'
END
NOTE1:-I have totally 4 different sql queries where there is different in case statement and for understanding purpose I gave 2 sql statements