增加不存在的数据

 

select siteID,
       siteName,
       categoryDesc,
       bucketId,
       servItemDesc,
       -- sub_details.year, 
       --price, 
       -- cost, 
       sub_years. year,
       sum((case
             when sub_details. year = sub_years. year then
              price
             else
              0 --default is 0 
           end)) price_new,
       sum((case
             when sub_details. year = sub_years. year then
              cost
             else
              0 -- default Is 0 
           end)) cost_new
  from (select cs.site_id siteID,
               cs.site_name siteName,
               cch.category_name categoryDesc,
               cwi.icam_bucket_id bucketId,
               cwi.work_item_desc servItemDesc,
               to_char(ce.event_start_dt, 'yyyy') year,
               nvl(decode(ces.actualize_ind,
                          'P',
                          ces.proj_price,
                          'A',
                          ces.act_price + ces.outstand_price,
                          'L',
                          ces.act_price,
                          0),
                   0) / TO_NUMBER(1000000) price,
               nvl(decode(ces.actualize_ind,
                          'P',
                          ces.proj_cost,
                          'A',
                          ces.act_cost + ces.outstand_cost,
                          'L',
                          ces.act_cost,
                          0),
                   0) / TO_NUMBER(1000000) cost
          from COST_MDL_STANDALONE_SERVICE mss,
               cost_site                   cs,
               COST_WORK_ITEM              cwi,
               cost_work_item_category     cwic,
               cost_category_head          cch,
               cost_event_service          ces,
               cost_event                  ce
         where ces.mdl_ver_id = 119545
           and ces.mdl_standalone_serv_id = mss.mdl_standalone_serv_id
           and ces.event_id = ce.event_id
           and ces.mdl_ver_id = ce.mdl_ver_id
           and cch.category_head_id = cwic.category_head_id
           and mss.mdl_ver_id = ces.mdl_ver_id
           and cs.site_id = mss.site_id
           and cwi.work_item_id = mss.work_item_id
           and cwic.work_item_category_id = cwi.work_item_category_id
        --  and ce.event_typ_id = 5 
        -- and cwi.work_item_desc = 'Vehicle Services' 
        ) sub_details,
       (select level + 2010 - 1 year
          from dual
        connect by level <= (2021 - 2010 + 1)) sub_years -- 2010 is first year in model ,2021 is last year in model 
-- 
 group by siteID,
          siteName,
          categoryDesc,
          bucketId,
          servItemDesc,
          sub_years. year

 

 

 

====================

 

select cs.site_id         siteID,
       cs.site_name       siteName,
       cch.category_name  categoryDesc,
       cwi.icam_bucket_id bucketId,
       cwi.work_item_desc servItemDesc,
       --to_char(ce.event_start_dt, 'yyyy') year,    
       sub_years. year,
       sum((case
             when to_char(ce.event_start_dt, 'yyyy') = sub_years. year then
              nvl(decode(ces.actualize_ind,
                         'P',
                         ces.proj_cost,
                         'A',
                         ces.act_cost + ces.outstand_cost,
                         'L',
                         ces.act_cost,
                         0),
                  0) / TO_NUMBER(1000000)
             else
              0
           end)) price_new, --      
       sum((case
             when to_char(ce.event_start_dt, 'yyyy') = sub_years. year then
              nvl(decode(ces.actualize_ind,
                         'P',
                         ces.proj_cost,
                         'A',
                         ces.act_cost + ces.outstand_cost,
                         'L',
                         ces.act_cost,
                         0),
                  0) / TO_NUMBER(1000000)
             else
              0
           end)) cost_new
  from COST_MDL_STANDALONE_SERVICE mss,
       cost_site cs,
       COST_WORK_ITEM cwi,
       cost_work_item_category cwic,
       cost_category_head cch,
       cost_event_service ces,
       cost_event ce,
       (select level + 2010 - 1 year
          from dual
        connect by level <= (2021 - 2010 + 1)) sub_years
 where ces.mdl_ver_id = 119545
   and ces.mdl_standalone_serv_id = mss.mdl_standalone_serv_id
   and ces.event_id = ce.event_id
   and ces.mdl_ver_id = ce.mdl_ver_id
   and cch.category_head_id = cwic.category_head_id
   and mss.mdl_ver_id = ces.mdl_ver_id
   and cs.site_id = mss.site_id
   and cwi.work_item_id = mss.work_item_id
   and cwic.work_item_category_id = cwi.work_item_category_id
--and ce.event_typ_id = 5    
--and cwi.work_item_desc = 'Vehicle Services'  
 group by cs.site_id,
          cs.site_name,
          cch.category_name,
          cwi.icam_bucket_id,
          cwi.work_item_desc,
          sub_years. year