Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f553JTE37203 for ; Mon, 4 Jun 2001 23:19:30 -0400 (EDT) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.4.1) with HTTP id 67107 for ; Mon, 04 Jun 2001 20:19:25 -0700 From: "Josh Berkus" Subject: Re: SQL Date Challenge To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.4.1 Date: Mon, 04 Jun 2001 20:19:25 -0700 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/33 X-Sequence-Number: 3236 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 ' DECLARE tc_period VARCHAR; tc_length INTERVAL; check_date DATE; first_date DATE; tc_window INTERVAL; first_week DATE; week_ends INT4; wday_diff INT4; BEGIN tc_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''); END LOOP; RETURN TRUE; END;' LANGUAGE 'plpgsql'; ENjoy! Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco