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.
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;
Hello Atlassian Community! My name is Emilee, and I’m a Product Marketing Manager for the Marketplace team. Starting with this post, I'm kicking off a monthly series of Spotlights to highlight Ma...
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!
Unfortunately there are no AUG chapters near you at the moment.Start an AUG
We're bringing product updates and pro tips on teamwork to ten cities around the world.Save your spot