Sum of 4 columns - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Sum of 4 columns

Author

Topic

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-03-22 : 07:31:51

Hi all,

I just ran into a problem with a query of mine.

I've made a asp frontend where a user enters his/hers time usage
for 2 static workdetails. lets just call the first workdetail a and the second b. A has HH:MM and B has HH:MM. In my database I had to devide them up into 4 columns, colummn a1 is HH and a2 is MM and b1 is HH and b2 is MM, all of the columns has the datatype int.

So when the user submit his/hers data, my query will then automatically sum all the work hours and minutes, and group them by what ever projectnumber the user enters. Here's my problem.. the sum isn't calculated correctly f.ex. a1 : 01 a2 : 30 in one projectnumber and in the same projectnumber but on a new date a1 : 02 a2 : 30 which should be a total of 4 hours.

how do i accomplish this ?

this is how my query looks like so far :

SELECT time_projectNumber, time_ParentMemID,Sum(time_tegneHours) AS [Sum Of time_tegneHours],
Sum(time_tegneMin) AS [Sum Of time_tegneMin],Sum(time_tegneIngHours) AS [Sum Of time_tegneIngHours],
Sum(time_tegneIngMin) AS [Sum Of time_tegneIngMin]
FROM Timesheet WHERE time_ParentMemID=2 GROUP BY time_projektNummer, time_ParentMemID ORDER BY time_projektNummer asc

my design of timesheet :
time_id ( pk )
time_createDate ( datetime )
time_startDateDay ( int )
time_startDateMonth ( int )
time_startDateYear ( int )
time_projectNumber ( varchar(255)
time_parentMemID ( int )
time_tegneHours ( int ) ( a1 )
time_tegneMin ( int ) ( a2 )
time_tegneIngHours ( int ) ( b1 )
time_tegneIngMin ( int ) ( b2 )

Anyone who can give me a hand in solving this, is very much appreciated.

Thanks

Best regards
Taz

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-22 : 08:49:00

logically you need to convert everything to the smallest unit, then do the math, then convert back to desired format. Here is one way:

sum((hr * 60) + mn) / 60 as [hours],
sum((hr * 60) + mn) % 60 as [minutes]

Be One with the Optimizer
TG

TazMania
Yak Posting Veteran

63 Posts

Posted - 2009-03-23 : 11:23:59

Thanks alot TG,

just what I was looking for.

Best regards
Taz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources