Re: Using a variable as a view name in a select

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:hforbes(at)dmr(dot)co(dot)uk]
Sent: April 3, 2007 12:45 PM
To: Wilkinson, Jim
Cc: pgsql-sql(at)postgresql(dot)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:hforbes(at)dmr(dot)co(dot)uk
<mailto:hforbes(at)dmr(dot)co(dot)uk> ]
Sent: April 3, 2007 10:14 AM
To: Wilkinson, Jim; pgsql-sql(at)postgresql(dot)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
**********************************************************