Solved

I need help!!!!

Posted on 2013-01-28
8
279 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
 

Author Comment

by:lulu50
ID: 38827010
yes they are the same
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

919 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

18 Experts available now in Live!

Get 1:1 Help Now