Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Update Table - Based on Sum of Records Grouped

    Hi guys,

    I have the following table
    Code:
    1	001	2014-09-01 00:00:00.000	BH	1-Z-128	0	180
    2	001	2014-09-01 00:00:00.000	BH	1-Z-999	0	300
    3	001	2014-09-01 00:00:00.000	CHO	1-Z-128	0	180
    4	001	2014-09-01 00:00:00.000	CHO	1-Z-999	0	306
    5	001	2014-09-01 00:00:00.000	OT1	1-Z-999	0	6
    6	001	2014-09-01 00:00:00.000	WRK	1-Z-128	0	180
    7	001	2014-09-01 00:00:00.000	WRK	1-Z-999	0	306
    8	002	2014-09-01 00:00:00.000	BH	1-Z-080	0	480
    9	002	2014-09-01 00:00:00.000	CHO	1-Z-080	0	480
    10	002	2014-09-01 00:00:00.000	WRK	1-Z-080	0	480
    11	002	2014-09-02 00:00:00.000	BH	1-Z-080	0	480
    12	002	2014-09-02 00:00:00.000	CHO	1-Z-080	0	600
    13	002	2014-09-02 00:00:00.000	OT1	1-Z-080	0	120
    14	002	2014-09-02 00:00:00.000	WRK	1-Z-080	0	600
    15	001	2014-09-02 00:00:00.000	BH	1-Z-128	0	480
    16	001	2014-09-02 00:00:00.000	CHO	1-Z-128	0	480
    What I want to do is update the table so that it populates the PERCENTAGE column on an empref/hrscode/date basis based on the sum of the WRK hours per day and empref.

    EG for 2014-09-01 for empref 001 the result would be
    Code:
    001	2014-09-01 00:00:00.000	BH	1-Z-128	37.037	180
    001	2014-09-01 00:00:00.000	BH	1-Z-999	61.728	300
    001	2014-09-01 00:00:00.000	CHO	1-Z-128	37.037	180
    001	2014-09-01 00:00:00.000	CHO	1-Z-999	62.963	306
    001	2014-09-01 00:00:00.000	OT1	1-Z-999	1.235	6
    001	2014-09-01 00:00:00.000	WRK	1-Z-128	37.037	180
    001	2014-09-01 00:00:00.000	WRK	1-Z-999	62.963	306
    IE Sum WRK = 486 so 180 is 37.037 percentage. Each HRSCODE hours total should total 100% (37.037 + 61.728)

    I can write a query to do this individually but how can I so this as a query for the full table.
    Code:
    declare @@total as float
    set @@total=(select SUM(hours) from tmsuser.tmswrhrs where hrscode='worked' and empref='001' and '2014-09-01 00:00:00.000'=procdate)
    update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/@@TOTAL*100)),12,3)) where  empref='001' and '2014-09-01 00:00:00.000'=procdate
    All help appreciated.

    Thanks,
    Conor
    Last edited by gvee; 10-21-14 at 13:23. Reason: Added [code] tags to improve formatting

  2. #2
    Join Date
    Sep 2011
    Posts
    44
    Hi All,

    I was able to get this working adding a new column to the table TOTALHRS and then the code below

    UPDATE tmsuser.TMSWRHRS SET totalhrs = (Select Sum(hours)
    From tmsuser.TMSWRHRS S
    Where S.empref=tmsuser.TMSWRHRS.empref AND S.procdate=tmsuser.TMSWRHRS.procdate and HRSCODE='WRK')

    update tmsuser.TMSWRHRS set PTAS1=(Str(((hours/totalhrs*100)),12,3))


    If anyone has a way to do this without the totalhrs addition it would be great as I had nhoped not to change the structure of this table.

    Thanks,
    Conor

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    EG for 2014-09-01 for empref 001 the result would be

    001 2014-09-01 00:00:00.000 BH 1-Z-128 37.037 180
    001 2014-09-01 00:00:00.000 BH 1-Z-999 61.728 300
    001 2014-09-01 00:00:00.000 CHO 1-Z-128 37.037 180
    001 2014-09-01 00:00:00.000 CHO 1-Z-999 62.963 306
    001 2014-09-01 00:00:00.000 OT1 1-Z-999 1.235 6
    001 2014-09-01 00:00:00.000 WRK 1-Z-128 37.037 180
    001 2014-09-01 00:00:00.000 WRK 1-Z-999 62.963 306
    Why percentage of 1st, 2nd and 5th rows were 37.037, 61.728 and 1.235, respectively?

    HRSCODE of 5th row was OT1, and it was different from HRSCODE of 1st and 2nd rows.


    By the way,
    you didn't show column names explicitly for the sample data.
    It made me to be troubled to understand your requirements exactly.
    Last edited by tonkuma; 10-21-14 at 13:26.

Posting Permissions

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