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) >='2006/04/01' AND date_trunc('month',incdate)<=date_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      ….
===============================================
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>='2007/04/01' AND date2<='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);
==================
Start_month  = april
End_month = 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
**********************************************************