Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2006
    Posts
    1

    Unanswered: Cumulative Field

    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...

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi,

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

    Code:
    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
    Last edited by Tyveleyn; 08-15-06 at 07:13.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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:
    Code:
    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)
    Code:
    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).

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Littlefoot
    On Oracle, you could create a database trigger
    The same holds for DB2.
    But replace NVL by COALESCE then. (Also works on Oracle.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Divardo
    ... 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):
    Code:
        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 a
    This way, you may insert into "oldtable", and read from "mytable".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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.

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Don't think this is possible...
    It's a feature of the dBforums lay-out, I'm afraid.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by Littlefoot
    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.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It may, but - on the other hand - it doesn't have to. Let me try:
    Code:
    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?
    Last edited by Littlefoot; 08-15-06 at 11:55.

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    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

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Never mind; we had a nice little chat here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •