Re: Using a variable as a view name in a select - Mailing list pgsql-sql

Jim<br /><br /> So let's suppose you have a "master" table of incidents<br /><br /> incident_no (serial)<br />
incident_date(timestamp)<br /> other fields<br /><br /> My understanding is that you now want to eg count the incidents
startingin a given month and going forwards for 12 months, grouping the results by month.  Have I understood the
problem?<br/><br /> If so here goes:<br /><br /> Set up a table hftest<br /><br /> incident serial<br /> incdate
timestamp<br/><br /> SELECT * from hftest;<br /> incident |       incdate<br /> ----------+---------------------<br />
    1000 | 2006-05-03 00:00:00<br />      1001 | 2006-04-03 00:00:00<br />      1002 | 2006-04-01 00:00:00<br />     
1003| 2006-12-08 00:00:00<br />      1004 | 2007-02-28 00:00:00<br />      1005 | 2007-08-03 00:00:00<br /><br />
Now:<br/> 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
BYdate_trunc('month',incdate) ORDER BY date_trunc('month',incdate);<br />  max | count<br /> -----+-------<br />  Apr
|    2<br />  May |     1<br />  Dec |     1<br />  Feb |     1<br />  <br />  which is almost what you want.  To get
themissing months with zeroes, I think you probably need a table of months and to use a left outer join but you may
havefound a better way by now!<br /><br />  Now I have NO idea on the efficiency of this as I rather suspect all those
date_truncfunctions may have an adverse effect!<br /><br /> Best regards<br /> Hilary<br />  <br /><br /><br /><br
/><br/><br /><br /> At 16:44 03/04/2007, you wrote:<br /><br /><blockquote cite="" class="cite" type="cite"><font
color="#000080"size="2">Hi Hilary, <br /> I am trying to produce reports where the user can select a different fiscal
yearstarting month.  From this I would select the correct table view to produce the reports in the correct month order
bycolumn<br />  <br /> Select * from table_view;<br />  <br /> Incident             April      May      June    
July     Aug      ….<br /> ===============================================<br /> Falls                 1         
0         1          0          0<br /> .<br /> .<br /> .<br /> .<br />  <br /> Can you think of another way to do this
?<br/>  <br />  <br /><hr /><div align="center"></div></font> <font face="Tahoma" size="2"><b>From:</b> Hilary Forbes
[<aeudora="autourl" href="mailto:hforbes@dmr.co.uk"> mailto:hforbes@dmr.co.uk</a>] <br /><b>Sent:</b> April 3, 2007
10:14AM<br /><b>To:</b> Wilkinson, Jim; pgsql-sql@postgresql.org<br /><b>Subject:</b> Re: [SQL] Using a variable as a
viewname in a select<br /></font><font face="Times New Roman, Times"> <br /> Jim<br /><br /> My initial reaction is
whatare you trying to achieve?  Surely you could have one underlying table with dates in it and<br /><br /> SELECT *
frommytable WHERE date1>='2007/04/01' AND date2<='2007/05/01';<br /><br /> but otherwise, like John, I would use
anexternal scripting language to create the table name.<br /><br /> Hilary<br /><br /> At 14:04 03/04/2007, Wilkinson,
Jimwrote:<br /><br /><br /></font><font color="#000080" face="Times New Roman, Times" size="2">I have created a view,
calledapril_may.   I need to select this view by combineing to fields in the database to create the view name etc …<br
/> <br /> Create view as select * from table_X;<br />  <br /> I need to do something like this … <br />  <br /> Select
*from (select table.start_month||_||table.end_month);<br /> ==================<br /> Start_month  = april<br />
End_month= May<br />  <br /> What I what to pass to the select is the combination of the 2 fields as the view name.<br
/> <br /> Any ideas ?<br /></font><br /><font face="Times New Roman, Times">Hilary Forbes<br /> DMR Limited (UK
registration01134804) <br /> A DMR Information and Technology Group company ( <a eudora="autourl"
href="http://www.dmr.co.uk/">www.dmr.co.uk</a>)<br /> Direct tel 01689 889950 Fax 01689 860330 <br /> DMR is a UK
registeredtrade mark of DMR Limited<br />
**********************************************************</font></blockquote><p>Hilary Forbes<br /> DMR Limited (UK
registration01134804) <br /> A DMR Information and Technology Group company (<a eudora="autourl"
href="http://www.dmr.co.uk/"><fontcolor="#0000FF"> <u>www.dmr.co.uk</u></font></a>) <br /> Direct tel 01689 889950 Fax
01689860330 <br /> DMR is a UK registered trade mark of DMR Limited<br />
**********************************************************