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:[EMAIL PROTECTED]]
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
**********************************************************
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
**********************************************************
