Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with summing fields by another field (SQL programming) 1

Status
Not open for further replies.

wheels0323

Programmer
Oct 15, 2008
17
US
I have this code.

select
laborlevelname2,
laborlevelname3,
personnum,
applydate,
laborlevelname4,
personfullname,
laboracctname,
timeinseconds,
Convert(VarChar(5), DateAdd(Minute, timeinseconds / 60, 0), 108) As HourMin,
wageamount,
laborleveldsc5
from vp_totals
where applydate >= '2007-01-01'
and applydate <= '2007-07-30'
and laborlevelname4 >= '00700'
and laborlevelname4 <= '00701'
and paycodename = '00 Total Worked Hours'
and laborlevelname3 > '05000'


The data it returns is showed in the attachment.
Iam creating a report to calculate total labor hours and wages for each person.

So, for a person, they might have 4 lines of data depending on how many days a person works.

Is their a way to sum their time worked(field - timeinseconds) and wages(field - wageamount). So that it shows up in one line per person. So on the picture below for Abel,Kit it returns one line with her total wages and total time worked. Yet, at the same time. The last field(laborleveldsc5), if they work in more then one department their would be two lines.

Let me know if you can help.
Thank you.
 
that won't work, because applydate is different. If you remove that column my suggestion will work. I you need that column that you will have to use a derived table to get all columns you need.
 
Thanks.

Convert(VarChar(2), sum(timeinseconds) / 3600) + ':' + Convert(VarChar(2), sum(timeinseconds) / 60 % 60) as TotalTime,

with this code for time calculations..
is their a way to get two decimal places?

so right now.. I have some solutions come back as 25:4
and I need another 0 on that.

The other convert time I was using in here wasen't working. any suggestions would be great.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top