Improve company productivity with a Business Account.Sign Up

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

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:
                  SELECT T.TaskStatusID,WD.StraightHours, WR.CompanyID
                  FROM workdone WD
                  LEFT JOIN Task T ON WD.TaskID = T.TaskID
                  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:
TaskStatusID  StraightHours   CompanyID
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

TaskStatusID  StraightHours   CompanyID
1             1.25                           103
1             0.5                          103
1            0.5                         103
1           1.5                         103
5            1                         103


TaskStatusID 5 is for complete task
TaskStatusID 1 is for Not completed task


HoursBilled    HoursExpectedToBeBilled
1                       3.75


Thank you!!!!!!!!!!
0
lulu50
Asked:
lulu50
1 Solution
 
kmslogicCommented:
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 Task T ON WD.TaskID = T.TaskID 
                  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 T.TaskStatusID, WR.CompanyID

Open in new window

0
 
lulu50Author Commented:
Hi kmslogic

the above query gives me this:

TaskStatusID  CompanyID   TotalStraightHours
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
 
DoutorApedeutaCommented:
The tables you posted, with your result and the result that you want are the same. Can you clarify the question?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
lulu50Author Commented:
yes they are the same
0
 
awking00Commented:
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 Task T ON WD.TaskID = T.TaskID
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
 
lulu50Author Commented:
awking00

this is beautifull

GREAT!!!!!!!!

THANK YOU THANK YOU AND THANK YOU
0
 
lulu50Author Commented:
EXCELLENT!!!!!!!!!!!!!!


Thank you for a million and one.
0
 
awking00Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now