On Fri, 2 Sep 2005, Henry Ortega wrote:
Thanks for all your answers. Very helpful.
What if after adding all those hours in one long transaction,
I want to send a query to check the MONTHLY TOTAL HOURS
(including those just entered)
and if they exceed N number of hours, all those records added
should *ROLLBACK*?
BEGIN;
insert..........
insert.........
if sum(hours)>N then ROLLBACK
END;
Is that possible? Maybe with just plain SQL? (and one transaction)
Just add in another where clause using AND and modify the values to sum
the hours for the entire month instead of just the day.
At least I think that would do it.
On 8/31/05, Philip Hallstrom <[EMAIL PROTECTED]> wrote:
On Wed, 2005-08-31 at 12:49 -0400, Henry Ortega wrote:
Ok. Here's TABLE A
emp date hours type
JSMITH 08-15-2005 5 WORK
JSMITH 08-15-2005 3 WORK
JSMITH 08-25-2005 6 WORK
I want to insert the ff:
1.) JSMITH 08-15-2005 8 VAC
2.) DOE 08-16-2005 8 VAC
#1 should fail because there is already 8 hours entered as being
Worked on 08-15-2005 (same date).
sorry, did not notice the duplicates before my previous reply.
you could do something like
insert into A select 'JSMITH','08-15-2005',8,'VAC'
where
8 != (select sum(hours) FROM A
WHERE emp = 'JSMITH'
AND date = '8-15-2005');
Wouldn't that fail if JSMITH had only worked 7 hours on 8-15? I'm
guessing he'd still want it to fail since adding that 8 hours ov VAC would
result in a 15 hour day... so maybe something like?
insert into A select 'JSMITH','08-15-2005',8,'VAC'
WHERE
8 >= 8 + (select sum(hours) FROM A
WHERE emp = 'JSMITH'
AND date = '8-15-2005');
?
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
