[SQL] generated dates from record dates - suggestions

#1

Hi folks.

I've got a table with three dates which are populated from an external source.
I then want to have a view with two calculated dates in it, e.g.

if date_1 is null and date_2 is null then date_a=NULL
if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
if date_1 > date3 rhen date_b=date1 else date_b=date2
etc.

What's the best way to do this?

I know it's a quite open question but I'm interested to hear different
responses

--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

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

#2

With a CASE ... WHEN statement?

Best,
Oliver

----- Original Message -----
From: "Gary Stainburn" <gary.stainburn (AT) ringways (DOT) co.uk>
To: <pgsql-sql (AT) postgresql (DOT) org>
Sent: Monday, August 20, 2012 1:17 PM
Subject: [SQL] generated dates from record dates - suggestions

Quote:

Hi folks.

I've got a table with three dates which are populated from an external
source.
I then want to have a view with two calculated dates in it, e.g.

if date_1 is null and date_2 is null then date_a=NULL
if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
if date_1 > date3 rhen date_b=date1 else date_b=date2
etc.

What's the best way to do this?

I know it's a quite open question but I'm interested to hear different
responses

--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

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

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

#3

On Tuesday 21 August 2012 02:01:55 Johnny Winn wrote:

Quote:

I would define a function and use it to abstract the expected behavior. It
would appear that there are several conditions so this abstraction would
provide for both usability and maintainability.

- Johnny

Johnny,

This was what I was wondering, and was looking for suggestions / best
practices on how to do this.

I had first thought of embedding case/when statements in the view but it could
easily become unweildy

--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

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

#4

On 2012-08-20, Gary Stainburn <gary.stainburn (AT) ringways (DOT) co.uk> wrote:

Quote:

Hi folks.

I've got a table with three dates which are populated from an external source.
I then want to have a view with two calculated dates in it, e.g.

if date_1 is null and date_2 is null then date_a=NULL
if date_1 is not null and date 2 is null then date_a=date_1+'90 days'
if date_1 > date3 rhen date_b=date1 else date_b=date2
etc.

What's the best way to do this?

CASE looks good.

Quote:

I know it's a quite open question but I'm interested to hear different
responses

"date_b" on line three, is that a typo? if not how am i to interpret
it?

--
⚂⚃ 100% natural

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

#5

On Tuesday 21 August 2012 12:14:53 Jasen Betts wrote:

Quote:

What's the best way to do this?

CASE looks good.

I know it's a quite open question but I'm interested to hear different
responses

"date_b" on line three, is that a typo? if not how am i to interpret
it?

The table contains date_1, date_2 and date_3. The resulting view needs to
contain date_1, date_2, date_3, date_a and date_b where date_a and date_b are
calculated based on the first three (plus a text field).

--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

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

#6

On Tuesday 21 August 2012 13:11:06 Johnny Winn wrote:

Quote:

CREATE OR REPLACE FUNCTION get_dates(date, date, date) RETURNS TABLE(date1
date, date2 date)
AS $$
DECLARE
date_1 DATE := NULL;
date_2 DATE := NULL;
BEGIN

-- test your conditions here

RETURN QUERY SELECT date_1::date, date_2::date;
END;
$$
LANGUAGE PLPGSQL;

I hope this helps,
Johnny

Johnny,

Having gone down the CASE/WHEN route and found it too clumsy I'm now looking
at using this method. I'm just about to start writing the function, but I'm
wondering how I would include this is the select / view .

Gary

--
Gary Stainburn
Group I.T. Manager
Ringways Garages
http://www.ringways.co.uk

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[SQL] generated dates from record dates - suggestions

mailing.database.pgsql-sql mailing.database.pgsql-sql

[SQL] generated dates from record dates - suggestions - 08-20-2012 , 07:17 AM

Re: [SQL] generated dates from record dates - suggestions - 08-20-2012 , 09:19 AM

Re: [SQL] generated dates from record dates - suggestions - 08-21-2012 , 05:31 AM

Re: [SQL] generated dates from record dates - suggestions - 08-21-2012 , 06:14 AM

Re: [SQL] generated dates from record dates - suggestions - 08-21-2012 , 07:15 AM

Re: [SQL] generated dates from record dates - suggestions - 08-22-2012 , 05:49 AM