Re: SQL Date Challenge

Since so many people responded to my initial question, I thought I'd
post my procedural solution using PL/pgSQL (permission granted to
Roberto to acquire it).  

I'm not gonna even try to explain the various references to my database
structure; there are too many.  This is all from StaffOS, which may soon
be an Open-Source project near you:

CREATE FUNCTION if_create_timecards_due ()
RETURNS BOOLEAN AS '
DECLAREtc_period VARCHAR;tc_length INTERVAL;check_date DATE;first_date DATE;tc_window INTERVAL;first_week
DATE;week_endsINT4;wday_diff INT4;
 

BEGINtc_window := fn_get_admin_value(''timecard window'');tc_period := fn_get_admin_value(''timecard
period'');week_ends:= to_number(fn_get_admin_value(''week ends''),''9'')::INT4;IF tc_period ~* ''^weekly'' THEN
tc_length:= interval(''7 days'');    first_date := current_date - tc_window;ELSE    tc_length := interval(''14 days'');
  first_week := to_date(fn_get_admin_value(''first week
 
ends''),''YYYY-MM-DD'');    first_date := current_date - tc_window;    IF (first_date - first_week)%14 < 7 then
first_date:= first_date + INTERVAL(''1 week'');    END IF;END IF;wday_diff := extract(dow FROM first_date);IF wday_diff
<=week_ends THEN    wday_diff := week_ends - wday_diff;ELSE    wday_diff = 7 - wday_diff + week_ends;END IF;first_date
:=first_date + interval(to_char(wday_diff, ''9'') || ''
 
days'');check_date := first_date;
DELETE FROM timecard_due_dates;
WHILE check_date <= current_date LOOP    INSERT INTO timecard_due_dates ( assignment_usq, week_ending )    SELECT
assignments.usq,check_date    FROM assignments    WHERE (status > 2 OR (status < 0 AND status > -81))        AND
start_date<= check_date        AND end_date > (check_date - tc_length);    check_date = check_date + interval(''7
days'');ENDLOOP;
 
RETURN TRUE;
END;'
LANGUAGE 'plpgsql';

ENjoy!
Josh

______AGLIO DATABASE SOLUTIONS___________________________                                      Josh Berkus Complete
informationtechnology      josh@agliodbs.com  and data management solutions       (415) 565-7293 for law firms, small
businesses       fax 621-2533   and non-profit organizations.      San Francisco