If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Determining EVERY month between 2 given dates
Hi everyone, I have a table with the following data upon which I would
like to report: SomeDt SomeValue 1-sep-09 100 12-dec-09 200 02-feb10 50 14-apr-10 75 I need to report on this data showing values for all months within the given min and max dates on the file so: These dates need to be part of a *rolling* report. I have data for Sep, Dec,Feb and Apr, but I want to display on the report ALL months regardless of whether they have data in the table and return 0 where they do not. Thus: Month Value Sep 09 100 Oct 09 0 Nov 09 0 Dec 09 200 Jan 10 0 Feb 10 50 Mar 10 0 Apr 10 75 I can get the min and max and the count of months easily enough: select min(SomeDt) as StDt, max(SomeDt) as EndDt, datediff("m" , StDt, EndDt) as CountofMonthsBetween from SomeTable What I cannot get is how to then determine what the months are in between. What I want if possible is a resultset that looks something like this StDt EndDt Mth 01/9/09 14/4/10 Sep 09 01/9/09 14/4/10 Oct 09 01/9/09 14/4/10 Nov 09 01/9/09 14/4/10 Dec 09 01/9/09 14/4/10 Jan 10 01/9/09 14/4/10 Feb 10 01/9/09 14/4/10 Mar 10 01/9/09 14/4/10 Apr 10 How can I do it? As much as possible I want to use QUERY resultsets, rather than creating intermediary tables etc which are just messy in MS Access, but will use them if no other choice. Happy to write a function if needs be to calc the Mth column. Am absolutely desperate so all help greatly appreciated. If it can't be done at all via queries alone, please let me know this too. Edwina63 |
Thread Tools | |
Display Modes | |
|
|