agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: matching against start/end times and diagnostic values
Date: Fri, 14 Nov 2014 11:04:52 -0600
Message-ID: <87sihl1znv.fsf@net82.ceos.umanitoba.ca> (raw)
References: <8761eqbwip.fsf@net82.ceos.umanitoba.ca>
	<545DEFED.8030200@bandenkrieg.hacked.jp>
	<87fvdskyet.fsf@net82.ceos.umanitoba.ca>
	<87fvdonl6i.fsf_-_@net82.ceos.umanitoba.ca>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

On Wed, 12 Nov 2014 15:49:57 -0600,
Seb <spluque@gmail.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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (2+ messages)

Message-ID: <87sihl1znv.fsf@net82.ceos.umanitoba.ca>
Permalink:  ../87sihl1znv.fsf@net82.ceos.umanitoba.ca/
Also on:    postgresql.org/message-id/87sihl1znv.fsf@net82.ceos.umanitoba.ca

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: spluque@gmail.com
  Subject: Re: matching against start/end times and diagnostic values
  In-Reply-To: <87sihl1znv.fsf@net82.ceos.umanitoba.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox