Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id D9F249FB1E4 for ; Tue, 3 Apr 2007 14:18:15 -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 81567-09 for ; Tue, 3 Apr 2007 14:18:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail102.cra-arc.gc.ca (mail102.cra-arc.gc.ca [198.103.184.24]) by postgresql.org (Postfix) with ESMTP id E8B269FA4F4 for ; Tue, 3 Apr 2007 14:18:03 -0300 (ADT) Received: from SD07ITMB01.PROD.NET (unknown [7.32.21.166]) by mail102.cra-arc.gc.ca (mail) with ESMTP id 6AF3207AVS; Tue, 3 Apr 2007 13:18:03 -0400 (EDT) Received: from SD01ITMV12.PROD.NET ([7.32.10.12]) by SD07ITMB01.PROD.NET with Microsoft SMTPSVC(6.0.3790.2499); Tue, 3 Apr 2007 13:18:03 -0400 x-mimeole: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C77614.09000613" Subject: Re: Using a variable as a view name in a select Date: Tue, 3 Apr 2007 13:18:02 -0400 Message-ID: <7CCC5BEF5E72394C963E529B54EB4A642A19BB@SD01ITMV12.PROD.NET> In-Reply-To: <200704031638.l33GcPe3025200@tamar.dmr.co.uk> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Using a variable as a view name in a select Thread-Index: Acd2DogFn5gPzqkyQEW58SN+iTGvcgABEOtQ From: "Wilkinson, Jim" To: "Hilary Forbes" Cc: X-OriginalArrivalTime: 03 Apr 2007 17:18:03.0105 (UTC) FILETIME=[09509D10:01C77614] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/37 X-Sequence-Number: 28037 This is a multi-part message in MIME format. ------_=_NextPart_001_01C77614.09000613 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Almost, in the table there are multiple different incidents. =20 =20 Incident April May June July Aug .... =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 Roof Area 0 1 0 0 2 Complaints.. 1 2 3 2 2=20 Etc ... =20 What I need to do is to be able to change the column heading to have a different start and finish month=20 Etc ... =20 Incident Feb Mar Apr May June .... =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 Roof Area 0 1 0 0 2 Complaints.. 1 2 3 2 2=20 =20 =20 The only way I can think of is to create 12 differents views with the months in order and then concatenating the start_month and end_month fields in the database to create the view name. Then do a select with the created view name. =20 Select * from May_June; . . . =20 ________________________________ From: Hilary Forbes [mailto:hforbes@dmr.co.uk]=20 Sent: April 3, 2007 12:45 PM To: Wilkinson, Jim Cc: pgsql-sql@postgresql.org Subject: RE: [SQL] Using a variable as a view name in a select =20 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 =20 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 =20 At 16:44 03/04/2007, you wrote: Hi Hilary,=20 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 =20 Select * from table_view; =20 Incident April May June July Aug .... =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 . . . . =20 Can you think of another way to do this ? =20 =20 ________________________________ From: Hilary Forbes [ mailto:hforbes@dmr.co.uk ]=20 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 =20 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 have 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 ... =20 Create view as select * from table_X; =20 I need to do something like this ...=20 =20 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 =20 What I what to pass to the select is the combination of the 2 fields as the view name. =20 Any ideas ? Hilary Forbes DMR Limited (UK registration 01134804)=20 A DMR Information and Technology Group company ( www.dmr.co.uk )=20 Direct tel 01689 889950 Fax 01689 860330=20 DMR is a UK registered trade mark of DMR Limited **********************************************************=20 Hilary Forbes DMR Limited (UK registration 01134804)=20 A DMR Information and Technology Group company ( www.dmr.co.uk )=20 Direct tel 01689 889950 Fax 01689 860330=20 DMR is a UK registered trade mark of DMR Limited ********************************************************** ------_=_NextPart_001_01C77614.09000613 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Almost,  in the table there = are multiple different incidents.

 

 

Incident    &nb= sp;        April      May      June     July      Aug      ….
=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           &n= bsp;            = 1          0          1          0          0
Roof Area       =        = 0          1  =          = 0         = 0          = 2            =  

Complaints..    = ;      = 1           2          = 3           = 2         2 =

Etc = …

 

=

What I need to do is to be able to = change the column heading to have a different start and finish month =

Etc = …

 

=

Incident    &nb= sp;        Feb      Mar      = Apr   May      June     ….
=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           &n= bsp;            = 1          0          = 1         0          0
Roof Area       =        = 0          1  =         = 0         = 0          = 2            =   

Complaints..    = ;      = 1          2 =          3         2  =         2 =

 

=

 

=

The only way I can think of is to = create 12 differents views with the months in order and then concatenating =  the start_month and end_month fields in the database to create the view = name.  Then do a select with the created view name.

 

=

Select  * from = May_June;


.
.
.

 

From: Hilary Forbes [mailto:hforbes@dmr.co.uk]
Sent: April 3, 2007 12:45 = PM
To: Wilkinson, Jim
Cc: = pgsql-sql@postgresql.org
Subject: RE: [SQL] Using = a variable as a view name in a select

 

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           = ;  April      May      June     July      Aug      ….
=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           &n= bsp;     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 have 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 = …
 
Create view as select * from table_X;
 
I need to do something like this …
 
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
**********************************************************

------_=_NextPart_001_01C77614.09000613--