Antonio,
Thank you very much. I will try your approach.
>Dmitry,
>
>Assuming that frequencies are submultiples of 12, or 12 itself:
>
>
>CREATE CURSOR fm (freq Int, mon Int)
>
>INSERT INTO fm VALUES (2, 4)
>INSERT INTO fm VALUES (6, 6)
>INSERT INTO fm VALUES (1, 3)
>INSERT INTO fm VALUES (2, 11)
>INSERT INTO fm VALUES (12, 1)
>
>SELECT freq, mon, mon AS cmo ;
> FROM fm ;
>UNION ;
>SELECT freq, mon, EVL((mon + 1) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq = 12 ;
>UNION ;
>SELECT freq, mon, EVL((mon + 2) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (6, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 3) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (4, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 4) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (3, 6, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 5) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq = 12 ;
>UNION ;
>SELECT freq, mon, EVL((mon + 6) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (2, 4, 6, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 7) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq = 12 ;
>UNION ;
>SELECT freq, mon, EVL((mon + 8) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (3, 6, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 9) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (4, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 10) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq IN (6, 12) ;
>UNION ;
>SELECT freq, mon, EVL((mon + 11) % 12, 12) AS cmo ;
> FROM fm ;
> WHERE freq = 12 ;
>INTO CURSOR calcFM
>
>SELECT * FROM calcFM WHERE cmo = 5
>
>
>>This is a simplified case.
>>
>>The given is a month number. For example, May is 5. Or it could be any month from 1 to 12.
>>
>>I have a table, SCHEDULE, which specifies frequency and a month no. For simplicity, this table has just two columns, FREQ and MONTH
>>
>>FREQ MONTH
>>2 4
>>6 6
>>1 3
>>2 11
>>12 1
>>
>>
>>The frequency is how many times a year. For example, frequency 6 means 6 times; frequency 2, 2 times.
>>
>>The Month specifies the month from which to determine the scheduled months. For example,
>>Frequency 2 and Month 4 means:
>>Month 4
>>Month 10
>>(6 months apart)
>>
>>Another example,
>>Frequency 12 and Month 1, mean every month. Or it could be Frequency 12 and month 2. Still, each month.
>>
>>My question, can I create a SQL Select of records where the “scheduled” month is 5 (or whatever given number).
>>That is, for example the row of FREQ 2 and Month 4 will not be included because they set the schedule for Months 4 and 10 (neither one is 5). But the row Frequency 2 and Month 11 will be selected since this record will create a schedule for Month 11 and Month 5.
>>
>>I know how to do it a long way. That is, scan each record of the above table, determine which months are affected and exclude those that don’t have the specified month.
>>
>>But I wonder if it can be done in one SQL Select
>>
>>TIA
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham