pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: pgsql-sql@postgresql.org
Subject: Re: SQL Date Challenge
Date: Mon, 04 Jun 2001 20:19:25 -0700
Message-ID: <web-67107@davinci.ethosmedia.com> (raw)
In-Reply-To: <Pine.LNX.4.30.0106042323430.768-100000@peter.localdomain>

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



view thread (9+ messages)

Message-ID: <web-67107@davinci.ethosmedia.com>
Permalink:  ../web-67107@davinci.ethosmedia.com/
Also on:    postgresql.org/message-id/web-67107@davinci.ethosmedia.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: josh@agliodbs.com
  Subject: Re: SQL Date Challenge
  In-Reply-To: <web-67107@davinci.ethosmedia.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox