with finanImpact as (
select EXTRACT(year from fiwm.create_dt) as years, m.code as mcode, sum(fiwm.rate) as rate from MASTER.financial_impact_wfins_mapping fiwm
inner join MASTER.wf_instance wf on wf.sysid=fiwm.wf_instance and wf.template_id=4
inner join MASTER.wf_data_cr wdc on wdc.instance_id=fiwm.wf_instance
inner join PARAMETER.module m on m.sysid=wdc.module and m.sysid in (4,15,6)
group by EXTRACT(year from fiwm.create_dt),m.code
union (
select null, m1.code,0 from parameter.module m1 where m1.sysid in (4,15,6)
minus
select null, m.code as mcode, 0 from MASTER.financial_impact_wfins_mapping fiwm
inner join MASTER.wf_instance wf on wf.sysid=fiwm.wf_instance and wf.template_id=4
inner join MASTER.wf_data_cr wdc on wdc.instance_id=fiwm.wf_instance
inner join PARAMETER.module m on m.sysid=wdc.module and m.sysid in (4,15,6)
group by EXTRACT(year from fiwm.create_dt),m.code
)
)
select fi.years, fi.mcode, fi.rate from finanImpact fi
union
(
SELECT LEVEL+2008-1,'',0 AS ID
FROM DUAL
CONNECT BY LEVEL <= 2012-2008+1
minus(
select distinct lfi.years,'',0 from finanImpact lfi
)
)
returned result:
=========================
2008 null 0
2009 null 0
2010 EB 34640
2010 GOF 8660
2011 EB 103920
2011 GOF 12990
2012 null 0
null ASD 0
=========================
goal:
=========================
2008 ASD 0
2009 ASD 0
2010 EB 34640
2010 GOF 8660
2011 EB 103920
2011 GOF 12990
2012 ASD 0
=========================
As above, howHow to write a sqlSQL to achieve the "goal" section? Select Select rate for all selected modules and selected years no matter they got rate for that year or not.
The years and modules will be input by users, then the sqlSQL will return the sum rate for selected years and selected modules. For
For instance, Find rate for ASD,EB,GOF modules from 2008 till 2012, as the above
WITH finanimpact
AS (SELECT Extract(YEAR FROM fiwm.create_dt) AS years,
m.code AS mcode,
SUM(fiwm.rate) AS rate
FROM master.financial_impact_wfins_mapping fiwm
inner join master.wf_instance wf
ON wf.sysid = fiwm.wf_instance
AND wf.template_id = 4
inner join master.wf_data_cr wdc
ON wdc.instance_id = fiwm.wf_instance
inner join parameter.module m
ON m.sysid = wdc.module
AND m.sysid IN ( 4, 15, 6 )
GROUP BY Extract(YEAR FROM fiwm.create_dt),
m.code
UNION
(SELECT NULL,
m1.code,
0
FROM parameter.module m1
WHERE m1.sysid IN ( 4, 15, 6 )
MINUS
SELECT NULL,
m.code AS mcode,
0
FROM master.financial_impact_wfins_mapping fiwm
inner join master.wf_instance wf
ON wf.sysid = fiwm.wf_instance
AND wf.template_id = 4
inner join master.wf_data_cr wdc
ON wdc.instance_id = fiwm.wf_instance
inner join parameter.module m
ON m.sysid = wdc.module
AND m.sysid IN ( 4, 15, 6 )
GROUP BY Extract(YEAR FROM fiwm.create_dt),
m.code)) SELECT fi.years,
fi.mcode,
fi.rate
FROM finanimpact fi
UNION
(SELECT LEVEL + 2008 - 1,
'',
0 AS id
FROM dual
CONNECT BY LEVEL <= 2012 - 2008 + 1
MINUS
(SELECT DISTINCT lfi.years,
'',
0
FROM finanimpact lfi))
returned result:
=========================
2008 null 0
2009 null 0
2010 EB 34640
2010 GOF 8660
2011 EB 103920
2011 GOF 12990
2012 null 0
null ASD 0
=========================
goal:
=========================
2008 ASD 0
2009 ASD 0
2010 EB 34640
2010 GOF 8660
2011 EB 103920
2011 GOF 12990
2012 ASD 0
=========================
