I have 5 select statements using union all(showing a few in here)
want to use a single statement to fetch the revenue from a table .
So based on the condition that AND period_id>=1009
AND Opp_Created_Date_Period_Id<=1009(for every previous quarters
where 1009 means yr 2018 Q2(also data contained in dim quater table looks like this only)
1008 - yr18 Q1
1007 - yr 17 Q4 (quarters)
and so on.
-want to fetch previous quarters .revenue
-most importantly period_id and Opp_Created_Date_Period_Id is to be compared separetly to each quarter separatly to produce its revenue.,for that quarter
-this period id value goes on decreasing up to five previous quarters
-these are parameters only
SELECT T1.PERIOD,NVL(Sum(Source_Rev),0) "SOURCE_REV" FROM MA_DIM_QUATER_DATA_TEST T1 LEFT JOIN
(
Select Sum(REVENUE_SHARE) As "SOURCE_REV" ,MIN(period_id) as PERIOD From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
AND period_id>=1009
AND Opp_Created_Date_Period_Id<=1009
UNION ALL
Select Sum(REVENUE_SHARE) As "SOURCE_REV",MIN(period_id) as PERIOD From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
AND period_id>=1009-1
AND Opp_Created_Date_Period_Id<=1009-1
UNION ALL
Select Sum(REVENUE_SHARE) As "SOURCE_REV",MIN(period_id) AS PERIOD From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
AND period_id>=1009-2
AND Opp_Created_Date_Period_Id<=1009-2
UNION ALL
Select Sum(REVENUE_SHARE) As "SOURCE_REV",MIN(period_id) as PERIOD From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
AND period_id>=1009-3
AND Opp_Created_Date_Period_Id<=1009-3
UNION ALL
Select NVL(Sum(REVENUE_SHARE),0) As "SOURCE_REV" ,MIN(period_id) as "PERIOD" From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
AND period_id>=1009-4
AND Opp_Created_Date_Period_Id<=1009-4
)T2 ON t1.id =t2.period
where t1.id in (1009,1009-1,1009-2,1009-3,1009-4)
GROUP BY t1.period
order by t1.period
Just focussing on T2, the following predicates are always true:
AND (GEO_ID=0 OR 0=0)
AND ( Strategic_Initiative_Id IN (0) OR ( COALESCE(NULL, 0))=0)
and the following is common to all queries
Where is_sourced!='Sourced'
So can't this be reduced to:
(
Select Sum(REVENUE_SHARE) As "SOURCE_REV" ,MIN(period_id) as PERIOD From Ma_Fact_Revenue_test
Where is_sourced!='Sourced'
AND
(
( period_id>=1009 AND Opp_Created_Date_Period_Id<=1009 )
or
( period_id>=1009-1 AND Opp_Created_Date_Period_Id<=1009-1 )
or
( period_id>=1009-2 AND Opp_Created_Date_Period_Id<=1009-2 )
or
( period_id>=1009-3 AND Opp_Created_Date_Period_Id<=1009-3 )
or
( period_id>=1009-4 AND Opp_Created_Date_Period_Id<=1009-4 )
)