Update Table - Based on Sum of Records Grouped

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Table - Based on Sum of Records Grouped

Author

Topic

cocono1
Starting Member

7 Posts

Posted - 2014-10-18 : 16:25:26

Hi guys,

I have the following table

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

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.

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

cocono1
Starting Member

7 Posts

Posted - 2014-10-18 : 17:17:29

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources