Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How to write a SQL to achieve the "goal" section? 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 SQL will return the sum rate for selected years and selected modules.

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
=========================
share|improve this question
    
glad to help, @50LV3R –  Sathya Nov 17 '11 at 9:23

1 Answer 1

up vote 1 down vote accepted

That looks like a homework question that is investigating how well you understand outer joins and maybe the NVL function (rather than the equijoins you currently have).

share|improve this answer
    
well, you remind me something which i have already forgotten, will look into that, its my job actually, im software engineer, but required to do some report. @Niall Litchfield –  50LV3R Nov 17 '11 at 9:39
    
I have figured out the solution for this questions, thanks for the tips Niall Litchfield. non-equijoins can solve this question @Niall Litchfield –  50LV3R Nov 21 '11 at 7:17

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.