SELECT p.title AS project, b.title AS plan,
(CASE WHEN LOWER(vl.plugin_key) LIKE '%:svn%' THEN 'Subversion' ELSE 
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:git%' THEN 'Git' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:p4%' THEN 'Perforce' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:hg%' THEN 'Mercurial' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:bb%' THEN 'BitBucket' ELSE
 CASE WHEN LOWER(vl.plugin_key) LIKE '%:gh%' THEN 'GitHub' ELSE
 vl.plugin_key
 END END END END END END) AS repository_type, 
(CASE WHEN bd.xml_definition_data LIKE '%<daily>%' THEN 'Single Daily Build' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<schedule>%' THEN 'Cron Based Scheduling' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<poll>%' AND bd.xml_definition_data LIKE '%CRON%' THEN 'Polling the Repository for changes - Scheduled' ELSE
 CASE WHEN bd.xml_definition_data LIKE '%<poll>%' AND bd.xml_definition_data LIKE '%PERIOD%' THEN 'Polling the Repository for changes - Periodically'
 END END END END) AS build_strategy,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<cronExpression>', bd.xml_definition_data)) + 16, 
        (LOCATE('</cronExpression>', bd.xml_definition_data) - 16 - LOCATE('<cronExpression>', bd.XML_DEFINITION_DATA)))) AS cron_expression,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<pollingPeriod>', bd.xml_definition_data)) + 15, 
        (LOCATE('</pollingPeriod>', bd.xml_definition_data) - 15 - LOCATE('<pollingPeriod>', bd.XML_DEFINITION_DATA)))) AS polling_period,
(SUBSTRING(bd.xml_definition_data, 
        (LOCATE('<buildTime>', bd.xml_definition_data)) + 11, 
        (LOCATE('</buildTime>', bd.xml_definition_data) - 11 - LOCATE('<buildTime>', bd.XML_DEFINITION_DATA)))) AS build_time
  FROM plan_vcs_location AS pvl 
  JOIN vcs_location      AS vl ON pvl.vcs_location_id = vl.vcs_location_id 
  JOIN build             AS b  ON b.build_id = pvl.plan_id
  JOIN build_definition  AS bd ON b.build_id = bd.build_id
  JOIN project           AS p  ON b.project_id = p.project_id
ORDER BY project, plan;