Solved

Need help with a mysql select statement

Posted on 2012-03-13
6
526 Views
Last Modified: 2012-06-27
Hi Experts
I have written a query and I am not sure where I have gone wrong.
What we need to achieve is:
1) we need to see how much Time_used id assigned to each project our team is working on.

however in my query I am unable to get the sum of hours assigned to each project.
I am ending up with the sum of all hours assigned to the user.

Sum(theme_keywords_pyramidpromo_assigned.TaskTimeSingle) is the column that holds all the hours assigned per project.

project = theme_keywords_pyramidpromo_assigned.profileid

user  =  usrprofiles.usrid

basically all i need to have the "Time_used" per project, per user for the month and any other work not finished in previous months.

in the attached txt file you will see that in the Time_used column rows 1,2 the values are the same

see column profile Name
credit repair
sarahdacruz.com

both are = 86

they should be
credit repair = 83
sarahdacruz.com = 3

Any suggestions will be appreciated

Query
======
SELECT
      usrprofiles.Department AS Department,
      usrprofiles.Email AS Email,
      usrprofiles.Title AS Title,
      usrprofiles.FirstName AS FirstName,
      usrprofiles.Surname AS Surname,
      projectprofile.profileName AS profileName,
      usrprofiles.AvailabilityDaysInMonth AS AvailabilityDaysInMonth,
      usrprofiles.AvailabilityHoursInDay AS AvailabilityHoursInDay,
      (usrprofiles.AvailabilityDaysInMonth * usrprofiles.AvailabilityHoursInDay)AS TotalHours,
      (SELECT Sum(theme_keywords_pyramidpromo_assigned.TaskTimeSingle)FROM theme_keywords_pyramidpromo_assigned WHERE theme_keywords_pyramidpromo_assigned.usrid = usrprofiles.usrid and theme_keywords_pyramidpromo_assigned.profileid = theme_keywords_pyramidpromo_assigned.profileid) AS Time_used,
      round(((usrprofiles.AvailabilityDaysInMonth * usrprofiles.AvailabilityHoursInDay) - sum(theme_keywords_pyramidpromo_assigned.TaskTimeSingle)), 2)AS Hour_Left
FROM
      usrprofiles
JOIN theme_keywords_pyramidpromo_assigned ON theme_keywords_pyramidpromo_assigned.usrid = usrprofiles.usrid
JOIN projectprofile ON projectprofile.profileid = theme_keywords_pyramidpromo_assigned.profileid
WHERE
usrprofiles.CompanyID = usrprofiles.CompanyID
AND theme_keywords_pyramidpromo_assigned.completed = 0
AND(MONTH(theme_keywords_pyramidpromo_assigned.dateDue) <= MONTH(now()))
AND(YEAR(theme_keywords_pyramidpromo_assigned.dateDue) = YEAR(now()))
GROUP BY
usrprofiles.usrid,
theme_keywords_pyramidpromo_assigned.profileid,
projectprofile.CompanyID
team-time-management.txt
0
Comment
Question by:matthewdacruz
  • 4
  • 2
6 Comments
 

Author Comment

by:matthewdacruz
ID: 37714127
No one know how to do this type of query?
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 37714355
I am not really into complex SQL queries, I would do this as a series of simple queries from PHP. Having said that, your GROUP BY has user as the primary grouping, should it not have the project as the primary grouping?

GROUP BY
projectprofile.CompanyID,
usrprofiles.usrid,
theme_keywords_pyramidpromo_assigned.profileid

rather than

GROUP BY
usrprofiles.usrid,
theme_keywords_pyramidpromo_assigned.profileid,
projectprofile.CompanyID
0
 

Author Comment

by:matthewdacruz
ID: 37714437
Hi bportlock
We need the GROUP BY usrprofiles.usrid, as there are multiple people working on a particular project. I f we group by just theme_keywords_pyramidpromo_assigned.profileid,
projectprofile.CompanyID then all of the team get lumped under a project.

We wanting to manage the teams workload and see what they currently working on.
Maybe there is a better way of writing the query I have?
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.

 

Accepted Solution

by:
matthewdacruz earned 0 total points
ID: 37714473
Got it figured out, it was the nested select that caused the issues.
should have just been  

Sum(theme_keywords_pyramidpromo_assigned.TaskTimeSingle)

instead of

 (SELECT Sum(theme_keywords_pyramidpromo_assigned.TaskTimeSingle)FROM theme_keywords_pyramidpromo_assigned WHERE theme_keywords_pyramidpromo_assigned.usrid = usrprofiles.usrid and theme_keywords_pyramidpromo_assigned.profileid = theme_keywords_pyramidpromo_assigned.profileid) AS Time_used,
0
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 37714652
Glad you are sorted out.
0
 

Author Closing Comment

by:matthewdacruz
ID: 37734351
I am closing this request as I figured out the issue.
The nsested select statement resulted in the wrong totals being found
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

895 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

12 Experts available now in Live!

Get 1:1 Help Now