Re: matching against start/end times and diagnostic values

On Wed, 12 Nov 2014 15:49:57 -0600,
Seb <spluque(at)gmail(dot)com> wrote:

> Sorry to come back with a related issue, which is proving troublesome.
> There's another log table, that looks just like voltage_log, but has
> an additional column with an integer indicating what problem occurred
> during the period:

> CREATE TABLE voltage_diagnostic_log ( record_id serial, time_beg
> timestamp without time zone NOT NULL, time_end timestamp without time
> zone NOT NULL, diagnostic integer, CONSTRAINT voltage_log_pkey PRIMARY
> KEY (record_id));

[...]

For posterity's sake, the only solution I was able to find was to first
create a view with a separate boolean column for each diagnostic value
via crosstab(). From there it was possible to use a WITH subquery to
remove rows with a particular diagnostic value (as suggested
previously), and then have the main SELECT statement do a second left
join to the crosstab view so that it could make use of the rest of the
boolean columns as sources for CASE statements for each field.

Cheers,

--
Seb