sql query --need some suggestions

Question

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?

Answer 1

Create a table containing months 1 to 12, cross join months with pm1_cycle_state, and left join with your select statement (probably as a view):

SELECT pm1_cycle_state.cycle_code, months.month
FROM pm1_cycle_state
CROSS JOIN months
LEFT OUTER JOIN V_Cycle_Months 
    ON V_Cycle_Months.cycd = pm1_cycle_state.cycle_code
    AND V_Cycle_Months.cm = months.month
WHERE V_Cycle_Months.cycd IS NULL

Answer 2

I got another better approach by lot of thinking. if i have a list like this :

102 1
102 10
102 11
102 12
103 1
103 10
103 11
103 12

then i can also get the list of existing entries in the table like below(lets say 103 11 is not present in the table ).Now my output of the query would be as below

102 1
102 10
102 11
102 12
103 1
103 10
103 12

since i have the above two lists i can simply do a minus of the two queries and get the required output as per the question i asked.And i did the same.and i got the expected output.Please put some comments if you wanna see the query!