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
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