Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 2E8019FB1EB for ; Tue, 3 Apr 2007 13:38:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 60758-01 for ; Tue, 3 Apr 2007 13:38:41 -0300 (ADT) X-Greylist: delayed 02:30:25.166875 by SQLgrey-1.7.4 Received: from tamar.dmr.co.uk (tamar.dmr.co.uk [195.112.52.166]) by postgresql.org (Postfix) with ESMTP id 1612E9FB5DB for ; Tue, 3 Apr 2007 13:38:40 -0300 (ADT) Received: from HFPORT.dmr.co.uk (pennsoft.gotadsl.co.uk [81.6.232.68]) by tamar.dmr.co.uk (8.13.5/8.13.5) with ESMTP id l33GcPe3025200; Tue, 3 Apr 2007 17:38:26 +0100 Message-Id: <200704031638.l33GcPe3025200@tamar.dmr.co.uk> X-Mailer: QUALCOMM Windows Eudora Version 7.1.0.9 Date: Tue, 03 Apr 2007 17:45:00 +0100 To: "Wilkinson, Jim" From: Hilary Forbes Subject: Re: Using a variable as a view name in a select Cc: pgsql-sql@postgresql.org In-Reply-To: <7CCC5BEF5E72394C963E529B54EB4A642A19B7@SD01ITMV12.PROD.NET > References: <200704031408.l33E81Wx015401@tamar.dmr.co.uk> <7CCC5BEF5E72394C963E529B54EB4A642A19B7@SD01ITMV12.PROD.NET> Mime-Version: 1.0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/36 X-Sequence-Number: 28036 Jim

So let's suppose you have a "master" table of incidents

incident_no (serial)
incident_date (timestamp)
other fields

My understanding is that you now want to eg count the incidents starting in a given month and going forwards for 12 months, grouping the results by month. Have I understood the problem?

If so here goes:

Set up a table hftest

incident serial
incdate timestamp

SELECT * from hftest;
incident |       incdate
----------+---------------------
     1000 | 2006-05-03 00:00:00
     1001 | 2006-04-03 00:00:00
     1002 | 2006-04-01 00:00:00
     1003 | 2006-12-08 00:00:00
     1004 | 2007-02-28 00:00:00
     1005 | 2007-08-03 00:00:00

Now:
SELECT max(to_char(incdate,'Mon')) ,count(incident) from hftest WHERE date_trunc('month',incdate) >=3D'2006/04/01' AND date_trunc('month',incdate)<=3Ddate_trunc('month',date '2006/04/01' + interval '12 months') GROUP BY date_trunc('month',incdate) ORDER BY date_trunc('month',incdate);
max | count
-----+-------
Apr |     2
May |     1
Dec |     1
Feb |     1

which is almost what you want. To get the missing months with zeroes, I think you probably need a table of months and to use a left outer join but you may have found a better way by now!

Now I have NO idea on the efficiency of this as I rather suspect all those date_trunc functions may have an adverse effect!

Best regards
Hilary

At 16:44 03/04/2007, you wrote:

Hi Hilary,
I am trying to produce reports where the user can select a different fiscal year starting month. From this I would select the correct table view to produce the reports in the correct month order by column

Select * from table_view;

Incident           &n= bsp; April      May      June     July      Aug      =85.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Falls            = ;     1          0          1          0          0
.
.
.
.

Can you think of another way to do this ?

From: Hilary Forbes [ mailto:hforbes@dmr.co.uk]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql@postgresql.org
Subject: Re: [SQL] Using a variable as a view name in a select

Jim

My initial reaction is what are you trying to achieve? Surely you could have one underlying table with dates in it and

SELECT * from mytable WHERE date1>=3D'2007/04/01' AND date2<=3D'2007/05/01';

but otherwise, like John, I would use an external scripting language to create the table name.

Hilary

At 14:04 03/04/2007, Wilkinson, Jim wrote:

I ha= ve created a view, called april_may.   I need to select this view by combineing to fields in the database to create the view name etc =85

Create view as select * from table_X;

I need to do something like this =85

Select * from (select table.start_month||_||table.end_month);
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Start_month =3D april
End_month =3D May

What I what to pass to the select is the combination of the 2 fields as the view name.

Any ideas ?

Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************

Hilary Forbes
DMR Limited (UK registration 01134804)
A DMR Information and Technology Group company ( www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************