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).
Any suggestions?
CREATE TABLE "tablea" (
"emp" varchar(6) NOT NULL,
"date" varchar(10) NOT NULL,
"hours" int NOT NULL,
"type" char(4) NOT NULL
);
grant select, insert, update, delete on tablea to public;
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 5, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-15-2005', 3, 'WORK' );
insert into tablea( emp, date, hours, type ) values( 'JSMITH',
'08-25-2005', 6, 'WORK' );
create or replace function overtime( varchar, varchar, int, varchar )
returns void as '
insert into tablea( emp, date, hours, type )
select $1, $2, $3, $4
from tablea where ( select sum( hours ) from tablea where emp =
$1 and date = $2 group by emp, date ) + $3 <= 8
union
select $1, $2, $3, $4
from tablea where( select sum( hours ) from tablea where emp = $1
and date = $2 group by emp, date ) is null
' LANGUAGE SQL;
select overtime( 'JSMITH', '08-15-2005', 8, 'VAC' ); # REJECTED
select overtime( 'JSMITH', '08-16-2005', 8, 'VAC' ); # OK
select overtime( 'JSMITH', '08-25-2005', 2, 'WORK' ); # OK
select * from tablea;
On 8/31/05, Ragnar Hafstað < [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> >
wrote:
On Wed, 2005-08-31 at 11:49 -0400, Henry Ortega wrote:
What I am trying to do is
* Insert a record for EMPLOYEE A to TABLE A
IF
the sum of the hours worked by EMPLOYEE A on TABLE A
is not equal to N
Is this possible?
Sure, given a suitable schema
It is not clear to me, if the hours worked are
to be found in the same table you want to insert
into, or not.
gnari
--
Daryl Richter
Platform Author & Director of Technology
v: 610.361.1000 x202
(( Brandywine Asset Management )
( "Expanding the Science of Global Investing" )
( http://www.brandywine.com ))
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
