MySQL query to show sum worklog timeworked for certain date range for users in certain group... Something is wrong?

I'm trying to pull the sum time worked for each user in a certain group over the next 30 days. I am not great at MySQL, so a lot of this may be wrong.

SELECT DISTINCT cu.display_name AS 'Associate', sum(wl.timeworked/3600) AS '30 Days'
    FROM worklog AS wl, cwd_user AS cu
    INNER JOIN cwd_membership AS cm
        ON cu.directory_id=cm.directory_id
        AND cu.lower_user_name=cm.lower_child_name
        AND cm.membership_type='GROUP_USER'
    INNER JOIN worklog
        ON worklog.AUTHOR = cu.user_name
    WHERE cm.lower_parent_name='atl_servicedesk_it_agents' AND wl.STARTDATE BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
 ORDER BY cu.last_name;

Right now it just displays the first users name with a sum total of everything in the time worked for everyone. That is obviously not what I want... If I remove the sum from timeworked it shows about half of the users from that group, but each user has a value of 16 hours for timeworked, which is incorrect. Only one user has any value for the next 30 days and it is for 16 hours, so for some reason it is duplicating this information.

Any ideas?

 

1 answer

1 accepted

Got it working by taking out the INNER JOINs.

SELECT cwd_user.display_name AS 'Associate', sum(worklog.timeworked/3600) AS '30 Days'
FROM worklog, cwd_user,cwd_membership
WHERE worklog.AUTHOR = cwd_user.user_name
and cwd_user.directory_id = cwd_membership.directory_id
and cwd_user.lower_user_name = cwd_membership.lower_child_name
and cwd_membership.membership_type = 'GROUP_USER'
and cwd_membership.lower_parent_name = 'atl_servicedesk_it_agents'
and worklog.STARTDATE BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 60 DAY)
GROUP BY cwd_user. display_name
ORDER BY cwd_user.last_name;

Suggest an answer

Log in or Sign up to answer
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published Thursday in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

77 views 0 5
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you