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
Go to Top of Page
   

- Advertisement -