I have a table with list of cycle codes.CYCLE_DEFINITION.
each and every cycle_code has 12 months entries in another table(PM1_CYCLE_STATE).
Each and every month has a cycle_start_date and a cycle_close_date.
i will check with a particular date(lets say sysdate) and check what is the current month of every cycle.additionally i will also get the list of future 3 more months of that particular cycle.
the query i have written is as below:
SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,LTRIM(pcs.cycle_month,'0')+0 CM, pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE
ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B')
UNION
SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+1,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+1) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE
ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B')
UNION
SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+2,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+2) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE
ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B')
UNION
SELECT cycd,cm,sd,ed,ld FROM (SELECT pcs.cycle_code CYCD,DECODE(LTRIM(pcs.cycle_month,'0')+3,13,1,14,2,15,3,LTRIM(pcs.cycle_month,'0')+3) CM ,pcs.cycle_start_date SD,pcs.cycle_close_date ED,ld.logical_date LD FROM pm1_cycle_state pcs,logical_date ld WHERE
ld.logical_date BETWEEN pcs.cycle_start_date AND pcs.cycle_close_date and ld.logical_date_type='B')
This query is running perfectly fine.
This will result in all the cycle_codes with exactly 4 rows for current month and future 3 months.
Now the requirement is if any of the month is missing.how could i show it?
for eg: the output of the above query is
cycd cm
102 1
102 10
102 11
102 12
103 1
103 10
103 11
103 12
104 1
104 10
104 11
104 12
Now lets say the row with cycd=104 and cm=11 is not present in the table,then the above query will not get the row 104 11.
I want to display only those rows.
how could i do it?
