x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 294

# I need help!!!!

Hi,

I have a query result.
what i want is to add up the StraighHours based on TaskStatusId
so, I need to sum up all straighthours based on the taskStatusID

Query:
FROM workdone WD
LEFT JOIN workrequest WR ON T.WRNumber = WR.WRNumber
LEFT JOIN MileageAndExpense ME ON WR.WRNumber = ME.WRNumber
where
WR.CompanyID = 103
AND ME.Billable = 'True'
AND (WorkDate >= '2013-01-01'
AND WorkDate <= '2013-01-31')
Group by CompanyID

My Output:
1             1.25                           103
1             0.5                          103
1            0.5                         103
1           1.5                         103
5            1                         103

the result that I want is

1             1.25                           103
1             0.5                          103
1            0.5                         103
1           1.5                         103
5            1                         103

HoursBilled    HoursExpectedToBeBilled
1                       3.75

Thank you!!!!!!!!!!
0
lulu50
1 Solution

Commented:
It wasn't clear what you wanted... Is it something like this?

``````SELECT T.TaskStatusID, WR.CompanyID, SUM(WD.StraightHours) as TotalStraightHours
FROM workdone WD
LEFT JOIN workrequest WR ON T.WRNumber = WR.WRNumber
LEFT JOIN MileageAndExpense ME ON WR.WRNumber = ME.WRNumber
where
WR.CompanyID = 103
AND ME.Billable = 'True'
AND (WorkDate >= '2013-01-01'
AND WorkDate <= '2013-01-31')
``````
0

Author Commented:
Hi kmslogic

the above query gives me this:

1                 103       3.75
5                 103                       1

but what I want the output to be is this

CompanyID    HoursBilled    HoursExpectedToBeBilled
103                        1                       3.75
0

Commented:
The tables you posted, with your result and the result that you want are the same. Can you clarify the question?
0

Author Commented:
yes they are the same
0

Commented:
SELECT WR.CompanyID,
SUM(CASE WHEN taskstatusid = 5 THEN WD.StraightHours ELSE 0 END) AS HoursBilled,
SUM(CASE WHEN taskstatusid = 1 THEN WD.StraightHours ELSE 0 END) AS HoursToBeBilled
FROM workdone WD
LEFT JOIN workrequest WR ON T.WRNumber = WR.WRNumber
LEFT JOIN MileageAndExpense ME ON WR.WRNumber = ME.WRNumber
WHERE WR.CompanyID = 103
AND ME.Billable = 'True'
AND (WorkDate >= '2013-01-01'
AND WorkDate <= '2013-01-31')
GROUP BY CompanyID;
0

Author Commented:
awking00

this is beautifull

GREAT!!!!!!!!

THANK YOU THANK YOU AND THANK YOU
0

Author Commented:
EXCELLENT!!!!!!!!!!!!!!

Thank you for a million and one.
0

Commented: