Re: list of all months

<div class="Section1"><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">I recently ran into the same issue and I resolved it by generating a table of nothing but months for the
last5 years:</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
 
color:#1F497D">select  TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') AS mmyyyy FROM
generate_series(1,60,1)AS s(a)</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">"2010-02"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">"2010-01"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">"2009-12"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">"2009-11"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">"2009-10"</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">…</span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">Then I did a join on this generated series:</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">SELECT months.mmyyyy</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">                ,COUNT(foo_key) </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">from      (</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">                select TO_CHAR((current_date - interval '1 month' * a),'YYYY-MM') as mmyyyy </span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";
 
color:#1F497D">                from generate_series(1,60,1) AS s(a)</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">                ) months </span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">                LEFT OUTER JOIN foo</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">                                ON months.mmyyyy=to_char(foo_date_created,'YYYY-MM')</span><p
class="MsoNormal"><spanstyle="font-size:11.0pt;font-family:"Calibri","sans-serif";
 
color:#1F497D">GROUP BY months.mmyyyy</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">I’m sure you can adapt this to your needs.</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D">Garrett Murphy</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D"> </span><div style="border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0in 0in 0in"><p
class="MsoNormal"><b><spanstyle="font-size:10.0pt;font-family:"Tahoma","sans-serif"">From:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]<b>On Behalf Of </b>query<br /><b>Sent:</b> Monday, March 08, 2010 5:25 AM<br
/><b>To:</b>pgsql-sql@postgresql.org<br /><b>Subject:</b> [SQL] list of all months</span></div><p class="MsoNormal"> <p
class="MsoNormal">Hi,<br/><br /> I want to display data for all days in a month even if no data exists for that month.
Someof the days in a month might not have any data at all. With normal query, we can display days only if data
exists.ButI want to display rows for all days in a month with blank data for non-existing day in database.<br /><br />
Howcan this be achieved ?<br /><br /><a
href="http://portal.mxlogic.com/redir/?atT74QSkQkjqtSkXI6zB5xAS03F8zG5ezW4DnunM5_FUTW4JPkapo-lyEH0fXkaokWfEitt4-nOAycdCT7PVg_w20ErAwwvV8TvAnXLcIELTKrKrm8Q5zOZ1oYLkN3Uw1lpp76zBc5blFcz7W2N_00jr5NPVJ5dNVNVNAsUrjodCBIo0saCBQQg7OFcQgltd46DDCy1mI9AW6V-7PM76Qjq9JeXb3bUVcQsTdxGcl-Vx8li0S"><span
style="text-decoration:none"><imgborder="0" id="_x0000_i1025" src="http://portal.mxlogic.com/images/transparent.gif"
/></span></a></div>