Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

I need help!!!!

Posted on 2013-01-28
8
Medium Priority
?
288 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Accepted Solution

by:
awking00 earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

636 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