Almost, in the table there are multiple different incidents.
Incident April May June July Aug .... =============================================== Falls 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 Feb Mar Apr May June .... ============================================== Falls 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:[EMAIL PROTECTED] 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) >='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] <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 <http://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 <http://www.dmr.co.uk/> ) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **********************************************************
