How to List Down Alternate Friday

Analytics
Enthusiast

How to List Down Alternate Friday

Dear who know how,
From the system calendar, i need to list down the date for every alternate friday start on 25 May 2007,

I only completed the SQL as below:

Sel Calendar_Date
,Week_of_Calendar
From Sys_Calendar.Calendar
Where Calendar_Date >= '2007-05-25'
And Day_of_Week=6

RESULT:
2007-05-25
2007-06-01
2007-06-08
2007-06-15
2007-06-22
2007-06-29
. . .

But I only need:
2007-05-25
2007-06-08
2007-06-22
. . .
Which is alternate friday since 25 May 2007.
Who know the solution please give me a hand. ^_^

Many thanks in advance.
Benjamin
4 REPLIES
Enthusiast

Re: How to List Down Alternate Friday


add this condition to the where clause

(day_of_calendar - 39226) MOD 14 = 0
Enthusiast

Re: How to List Down Alternate Friday

Thanks a lot.

May I know what is the function of 'MOD'?

I am new, very appreciate your teaching. :-)

Thanks
BS
Enthusiast

Re: How to List Down Alternate Friday

mod is a mathematical function that gives you the remainder of a division operation. (same as % operator in normal programming languages like C/C++/JAVA etc)

so

3 MOD 4 = 3
5 MOD 4 = 1
8 MOD 4 = 0

and so forth.

I used the logic that day of calendar for the first friday under consideration is 39226 and so if I subtract 39266 from the day_of_calendar column I will get results of 0, 7, 14, 21 ... etc for all the Fridays. so if I wanted to get only the alternate Friday, then all I have to do is do a "mod" of that difference with 14 and check if the remainder is zero.

so

0 MOD 14 = 0 (selected)
7 MOD 14 = 7 (not selected)
14 MOD 14 = 0 (selected)
21 MOD 14 = 7 (not selected)

...........

Enthusiast

Re: How to List Down Alternate Friday

Thank you! :-)