Greg B Roberts Greg B Roberts - 6 months ago 30
MySQL Question

[My]SQL Query Syntax which requires foreach processing

I have an SQL query task which I can do OK in C# or linqpad but would prefer it is in SQL so standard reporting tools can do it.

End DB is a bugzilla under MySQL



The problem is I need to loop through the bug_activity looking for particular changes to consider the parent record "valid", how ?
e.g. pseudo logic like



if bug_status went to IN BACKLOG
then bug_status went to ASSIGNED
and this happened 2016-03-01 to 206-03-31
then consider valid record




I am unsure how to do this as web examples only show DECLARE and LOOPs but how a loop fits into the "select, from, where" code.



set @BugID = 64252;

select
bugs_activity.bug_id,
-- profiles.realname,
-- profiles.login_name,
bugs_activity.bug_when,
fielddefs.name,
bugs_activity.added
-- bugs_activity.removed
from
bugs_activity,
profiles,
fielddefs

-- Real world 'Where xx' will have more logic and result in a number of bugzilla records
-- Each bugzilla record has its own 'bugs_activity'
-- Logic needs to look at each buzilla records historyto filter results
-- Want to end up with a filtered record set and a total number of records
Where bug_id = @BugID AND bugs_activity.who = profiles.userid AND bugs_activity.fieldid = fielddefs.id


Example of bug_activity

bug_id bug_when name added
64252 26/01/2016 6:51:30 AM status_whiteboard ID:103138574
64252 26/01/2016 6:52:10 AM cc xxx@abc.com
64252 28/01/2016 9:49:10 AM bug_status IN BACKLOG
64252 28/01/2016 9:49:10 AM cf_escalation_notes Effort: 2
Reproduced by support
64252 28/01/2016 9:49:10 AM assigned_to def@abc.com
64252 2/05/2016 4:33:05 PM bug_status ASSIGNED

Answer
SELECT 
  bug_id,
  SUM(CASE WHEN bug_status='IN BACKLOG' THEN 1 ELSE 0 END) as backlogCount,
  SUM(CASE WHEN bug_status='ASSIGNED' THEN 1 ELSE 0 END) as assignedCount
FROM bugs_activity
WHERE action_date BETWEEN '2016-03-01' AND '206-03-31'
GROUP BY bug_id
HAVING backlogCount>0 AND assignedCount>0

The select returns bug_ids which were in 'IN BACKLOG' and in 'ASSIGNED' statuses during the period. You can use the query above in your FROM section instead of bugs_activity

UPDATE:

Add in the SELECT section

  MAX(CASE WHEN bug_status='IN BACKLOG' THEN action_date ELSE NULL END) as backlogDate,
  MAX(CASE WHEN bug_status='ASSIGNED' THEN action_date ELSE NULL END) as assignedDate

and then in HAVING section AND backlogDate<assignedDate