## Message Posted: Sun, 10 Oct 2004 @ 11:42:27 GMT

 < Last>>

 Subj: Re: Work Week based on Month From: Anomy Anom

<-- Anonymously Posted: Saturday, October 09, 2004 12:18 -->

Although not very elegant, the following code works for the needed date range, without any gaps:

```     SELECT
T2.year_of_calendar
,T2.month_of_year
,CASE
WHEN day_of_week = 6 and week_of_month = 0
THEN  calendar_date - 6
WHEN day_of_week = 6 and week_of_month = 1 and day_of_month BETWEEN 6 and 7
THEN  calendar_date - 6
END startdate
,T1.enddate

FROM ( SELECT
year_of_calendar
,month_of_year
,CASE
WHEN week_of_month = 4 and weekday_of_month = 5
THEN  calendar_date
WHEN week_of_month = 3 and weekday_of_month = 4
and month_of_year IN (1,3,5,7,8,10,12) and day_of_month >= 25
THEN  calendar_date
WHEN  week_of_month = 4 and weekday_of_month = 4
and month_of_year IN (1,3,5,7,8,10,12) and day_of_month >= 27
THEN  calendar_date
WHEN  week_of_month = 3 and weekday_of_month = 4
and month_of_year IN (4,6,9,11) and day_of_month BETWEEN 24 and 26
THEN  calendar_date
WHEN  week_of_month = 4 and weekday_of_month = 4
and month_of_year IN (4,6,9,11) and day_of_month >= 27
THEN  calendar_date
WHEN day_of_week = 6 and week_of_month = 3 and weekday_of_month = 4
and month_of_year IN (2) and year_of_calendar MOD 4 = 0 and day_of_month >= 24
THEN  calendar_date
WHEN day_of_week = 6 and week_of_month = 4 and weekday_of_month = 4
and month_of_year IN (2) and year_of_calendar MOD 4 = 0 and day_of_month >= 27
THEN  calendar_date
WHEN day_of_week = 6 and week_of_month = 3 and weekday_of_month = 4
and month_of_year IN (2) and year_of_calendar MOD 4 <> 0 and day_of_month >= 22
THEN  calendar_date
WHEN day_of_week = 6 and week_of_month = 4 and weekday_of_month = 4
and month_of_year IN (2) and year_of_calendar MOD 4 <> 0 and day_of_month >= 27
THEN  calendar_date
END enddate

FROM sys_calendar.calendar
where year_of_calendar BETWEEN 2004 and 2007 and enddate IS NOT NULL and
day_of_week = 6 ) T1, sys_calendar.calendar T2

WHERE startdate is not null
and T1.year_of_calendar= T2.year_of_calendar
and T1.month_of_year  = T2.month_of_year
order by 1,2
```

 < Last>>

Attachments

Library

Quick Reference

Archives

Sample Index

 2016 2007 2015 2006 2014 2005 2013 2004 2012 2003 2011 2002 2010 2001 2009 2000 2008 1999

2004 Indexes

 Jan Jul Feb Aug Mar Sep Apr Oct May Nov Jun Dec

 Top Home Privacy Feedback