On 8/28/06,
Henry Ortega <
juandelacruz@gmail.com> wrote:
Here's what I am doing:
I have this table:
employee payrate effective tstamp end_date (to be updated by trigger)
jdoe 1000 04-01-2006 2006-03-10 13:39: 07.614945
jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325
jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444
peter 500 04-1-2006 2006-03-25 08:13:35.152166
peter 900 04-16-2006 2006-03-28 09:22:14.456221
After the trigger runs, I want to have this:
employee payrate effective tstamp end_date (to be updated by trigger)
jdoe 1000 04-01-2006 2006-03-10 13:39:07.614945 04-15-2006
jdoe 1500 04-01-2006 2006-03-12 15:43:14.423325 04-15-2006
jdoe 1555 04-16-2006 2006-03-15 12:14:15.112444 NULL
peter 500 04-1-2006 2006-03-25 08:13:35.152166 04-15-2006
peter 900 04-16-2006 2006-03-28 09:22:14.456221 NULL
You may want to separate this into two tables:
employee_day
employee_day_id bigserial
employee_name varchar(100)
effective_day date
effective_pay
effective_pay_id bigserial
tstamp timestamp
employee_day_id (foreign key)
Then you can calculate end_day off the employee_day table (you can do this without splitting out the table however splitting the table means fewer records for this query and a more normalized database):
SELECT
ed1.employee_day_id,
ed1.employee_name,
ed1.effective_day,
case ed2.effective_day
when null then null
else min(ed2.effective_day) - interval '1 day'
end
FROM employee_day ed1
LEFT OUTER JOIN employee_day ed2 ON (
ed1.employee_name = ed2.employee_name
AND
ed1.effective_day < ed2.effective_day
)
Since it appears end_date is a derived value, you can use the above query as a view. I guess there could be performance concerns but then you could create a materialized view for it.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com http://codeelixir.com==================================================================
