Solved

I need help!!!!

Posted on 2013-01-28
8
280 Views
Last Modified: 2013-01-28
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
Comment
Question by:lulu50
8 Comments
 
LVL 16

Expert Comment

by:kmslogic
ID: 38826973
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
 

Author Comment

by:lulu50
ID: 38826999
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
 
LVL 1

Expert Comment

by:DoutorApedeuta
ID: 38827003
The tables you posted, with your result and the result that you want are the same. Can you clarify the question?
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:lulu50
ID: 38827010
yes they are the same
0
 
LVL 32

Accepted Solution

by:
awking00 earned 500 total points
ID: 38827058
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
 

Author Comment

by:lulu50
ID: 38827107
awking00

this is beautifull

GREAT!!!!!!!!

THANK YOU THANK YOU AND THANK YOU
0
 

Author Closing Comment

by:lulu50
ID: 38827119
EXCELLENT!!!!!!!!!!!!!!


Thank you for a million and one.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38827383
Glad to help.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now