Wednesday, May 14, 2014

Query - Generating missing months

select * FROM (
             SELECT DISTINCT
                    md.inventory_item_id
                  , md.organization_id
                  , md.plan_id
                  , md.plan_run_id
                  , md.project_id
                  , md.sr_instance_id
                  , md.vmi_flag
                  , md.io_plan_flag
                  , md.owning_org_id
                  , md.owning_inst_id
                  , md.aggr_type
                  , md.category_set_id
                  , md.sr_category_id
                  , EXTRACT(YEAR FROM md.order_date) years
                  , cal.min_month_date
                  , cal.max_month_date
               FROM msc_demands_f md
                  , (
                         SELECT TO_NUMBER(year_name) year_name
                              , MIN(mpd.month_start_date) min_month_date
                              , MAX(mpd.month_end_date) max_month_date
                           FROM msc_phub_dates_mv mpd
                          GROUP BY TO_NUMBER(year_name)
                                 , month_name
                    ) cal
              WHERE cal.year_name = EXTRACT(YEAR FROM md.order_date)
                AND cal.min_month_date >= (
                                              SELECT MIN(mpd.month_start_date)
                                                FROM msc_demands_f mdf
                                                   , msc_phub_dates_mv mpd
                                               WHERE mpd.calendar_date = mdf.order_date
                                                 AND mdf.inventory_item_id = md.inventory_item_id
                                                 AND mdf.organization_id = md.organization_id
                                                 AND mdf.plan_id = md.plan_id
                                                 AND mdf.plan_run_id = md.plan_run_id
                                         )
                AND cal.min_month_date <= (
                                               SELECT MAX(mpd.month_start_date)
                                                 FROM msc_demands_f mdf
                                                    , msc_phub_dates_mv mpd
                                                WHERE mpd.calendar_date = mdf.order_date
                                                  AND mdf.inventory_item_id = md.inventory_item_id
                                                  AND mdf.organization_id = md.organization_id
                                                  AND mdf.plan_id = md.plan_id
                                                  AND mdf.plan_run_id = md.plan_run_id
                                          )
             ) md_out
  WHERE 1=1
    AND md_out.inventory_item_id = 1369
    AND md_out.organization_id = 593
    AND md_out.plan_id = 2022
    AND md_out.plan_run_id = 91911
  ORDER BY min_month_date