From: | "Josh Berkus" <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: SQL Date Challenge |
Date: | 2001-06-05 03:19:25 |
Message-ID: | web-67107@davinci.ethosmedia.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
From | Date | Subject | |
---|---|---|---|
Next Message | Andrzej Roszkowski | 2001-06-05 06:18:03 | PL/PGSQL |
Previous Message | Josh Berkus | 2001-06-05 01:56:36 | Re: Huh? Data typing bug? |