596

[My]SQL Query Syntax which requires foreach processing

Question:

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

<blockquote> <blockquote>

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

</blockquote> </blockquote> 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 <blockquote> <blockquote>

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.

</blockquote> </blockquote> 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

Answer1:

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

Recommend

  • How can I suppress java compiler warnings about Sun proprietary API
  • Java version names in Sun's bug database
  • SQL: Join on two tables where on column is null
  • Spring cloud config server not loading the properties files
  • How to add new collection criteria in Plone 5?
  • signer not valid error
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • Setting the run time properties on SpringApplicationBuilder()
  • SAVE attribute needed for Fortran variables when only the C_LOC address is returned to a C program?
  • Eliminate partial duplicate rows from result set
  • pyodbc doesn't report sql server error
  • Conversion from string “a” to type 'Boolean' is not valid
  • Array with custom indexes in Ionic2
  • Grails calculated field in SQL
  • Sort List of Strings By Version
  • Set the selected item in dropdownlist in MVC3
  • jQuery .attr() and value
  • Highlight one bar in a series in highcharts?
  • Getting last autonumber in access
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • Javascript + PHP Encryption with pidCrypt
  • Display Images one by one with next and previous functionality
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • KeystoneJS: Relationships in Admin UI not updating
  • AngularJs get employee from factory
  • Load html files in TinyMce
  • Acquiring multiple attributes from .xml file in c#
  • How to set the response of a form post action to a iframe source?
  • How do you join a server to an Active Directory (domain)?
  • How to CLICK on IE download dialog box i.e.(Open, Save, Save As…)
  • Change div Background jquery
  • How does Linux kernel interrupt the application?
  • Qt: Run a script BEFORE make
  • How can I remove ASP.NET Designer.cs files?
  • reshape alternating columns in less time and using less memory
  • Conditional In-Line CSS for IE and Others?
  • java string with new operator and a literal