I have four columns in a table. One called person, days, hours and cumulative. Every day the person works and as a result creates a new entry and thus an entire new row. From this, the day increases e.g. 1 - 2 - 3 etc in each row. The hours that they work is different each day. What i am hoping to achieve is have the forth column showing the total hours that person has work this the first day they started. Any ideas...

Hi,

Since you posted your question in the SQL forum I presume that you're looking for a query. Her I've got one:

SELECT person, days, hours,
(SELECT sum(hours)
FROM table b
WHERE b.person = a.person
AND b.days <= a.days) AS cumulative
FROM table a

This should work, at least it does on an Informix database. I'm not shure whether this is standard SQL or not...

If it's not a query you want but a way of dynamicly filling the fourth column at every insert you could use the subquery inside a trigger to calculate the value to be inserted in the last column.

Regards

Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean; for example:CREATE OR REPLACE TRIGGER trg_hours
BEFORE INSERT ON HOURS_CUM
FOR EACH ROW
BEGIN
SELECT
NVL(MAX(t.days) + 1, 1),
NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
INTO :NEW.days, :NEW.cumulative
FROM HOURS_CUM t
WHERE person = :NEW.person;
END;Direct table insert won't be possible (in Oracle) because table is mutating and records can't be accessed (for example, entering 2 hours for person number 1)INSERT INTO HOURS_CUM
(person, days, HOURS_worked, cumulative)
(SELECT
1,
NVL(MAX(t.days) + 1, 1),
2,
NVL(SUM(t.HOURS_worked), 0) + 2
FROM HOURS_CUM t
WHERE t.person = 1
);I guess the same goes for another databases; if you can't use triggers, you'll need to figure out how to bypass such a limitation (if it exists).

On Oracle, you could create a database trigger
The same holds for DB2.
But replace NVL by COALESCE then. (Also works on Oracle.)

... have the forth column showing the total hours ...
An other solution consists of redefining your table:
- Have a differently named table with just the first three columns.
- Create a VIEW (with the name of the old table): CREATE VIEW mytable AS
SELECT person, days, hours,
(SELECT SUM(hours)
FROM oldtable
WHERE person = t.person AND days <= t.days) AS cumulative
FROM oldtable AS aThis way, you may insert into "oldtable", and read from "mytable".

Can anyone tell me how to size the "Code" section so it won't appear with a fixed size and a scrollbar? I've been trying some things now, without the wanted result...

Thanks,
Hans

BTW: With Informix it's possible to dynamically store a value in a column of an inserting row. It has to be done with an insert trigger that a invokes a stored function which returns it's value INTO the specified column.

Don't think this is possible...
It's a feature of the dBforums lay-out, I'm afraid.

Which database do you use? On Oracle, you could create a database trigger which would populate the required columns; this would be a nice and clean;

Unfortunatrely this trigger solution posted may produce a "mutating table" error. :rolleyes:

It may, but - on the other hand - it doesn't have to. Let me try:SQL> create table hours_cum
2 (person number,
3 days number,
4 hours_worked number,
5 cumulative number);

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_hours
2 BEFORE INSERT ON HOURS_CUM
3 FOR EACH ROW
4 BEGIN
5 SELECT
6 NVL(MAX(t.days) + 1, 1),
7 NVL(SUM(t.HOURS_worked), 0) + :NEW.HOURS_worked
8 INTO :NEW.days, :NEW.cumulative
9 FROM HOURS_CUM t
10 WHERE person = :NEW.person;
11 END;
12 /

Trigger created.

SQL> insert into hours_cum (person, hours_worked) values (1, 8);

1 row created.

SQL> select * from hours_cum;

PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8

SQL> insert into hours_cum (person, hours_worked) values (1, 7);

1 row created.

SQL> select * from hours_cum;

PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15

SQL> insert into hours_cum (person, hours_worked) values (2, 5);

1 row created.

SQL> select * from hours_cum;

PERSON DAYS HOURS_WORKED CUMULATIVE
---- ---- ---- ----
1 1 8 8
1 2 7 15
2 1 5 5

SQL>It seems quite OK to me ... did you have something else in mind, what I don't see at the moment?

In summary, there's not much difference between the "trigger" solution and the "view" solution -- i.e., you insert into the first three columns, and you may read the four columns.
Advantages of the "trigger" solution:
- only need one table in all SQL
- 4th column is calculated once, and stored; no recalculation on read
Advantages of the "view" solution:
- less danger of attempt to insert into 4th column, since the table to be inserted only has 3 columns, and an attempt to insert into the view will tell you it's a view
- a trigger is invisible for SQL; with the view it is clearer for the SQL user that the 4th column is a calculated one.

For what it's worth, I would prefer the view-method. Next to the advantages Peter summarized it complies with the first normalform to!

BTW: Did anyone notice the initial asker, Divardo, never replied to this thread?

Bye

Never mind; we had a nice little chat here :)