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;
-- 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
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 email@example.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 firstname.lastname@example.org
64252 2/05/2016 4:33:05 PM bug_status ASSIGNED
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
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