Changes between Version 33 and Version 34 of statusdatabase


Ignore:
Timestamp:
Oct 16, 2014 11:53:09 AM (5 years ago)
Author:
tec
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • statusdatabase

    v33 v34  
    2929
    3030OK onto the fun stuff, pivot tables. The most evil nasty piece of poo you can think to do with databases.
     31
     32This is the view (`not_started_sensors_pivot`) that provides the main content for the `not_started_sensors` view
     33All it does is finds all the sensors that have not been started or are blocked, it then uses the "crosstab" function to essentially group things by flight_id and priority. Google crosstab, its hard to explain and it only gets worse. The cross tab takes all the row with the same flight line and puts them onto the same row. The quotes inside of the crosstab have been escaped, to escape a `'` you write `''`.
    3134{{{
    32 #!html
    33 <pre>
     35#!sql
    3436CREATE OR REPLACE VIEW not_started_sensors_pivot AS
    3537SELECT *
     
    4446ORDER BY p.value DESC, ss.submit_time, fs.sensor_name ASC')
    4547AS ct(id int, c1 text, c2 text, c3 text, c4 text, c5 text, c6 text, c7 text, c8 text, c9 text);
    46 </pre>
    4748}}}