Year and month filter

[kattaviz]

Hi,

My requirement is to return data for 3 Months and 2 Quarters based on the Year and month selected.

For Example: If user has selected March 2010 i would like to show March 2010,Feb 2010, Jan 2010 , Q1 of 2010 and Q4 of 2009.

I think i have to use 2 queries and join them. Does somebody has any ideas how to achieve this.

thanks in advance.

[Feanor]

Do you use a relational FrameWork model? I'm guessing so since it wouldn't be to hard then.

For relational, the best long term solution I think is to implement a time dimension within your DWH-architecture. You can then join the time dimension with your facts (for example: order, or returns fact table) on a DATE_KEY or MONTH_KEY. Then it's not to hard to collect data of the last number of days, months, quarters, years, etc. Because you don't use any functions to calculate quarter in real time, depending on the amount of data this also gives you a fast performing solution.

If you can't or don't want to use a time dimension then another strategy is to calculate the quarter for each date. With the quarter available it's also pretty easy to answer to your requirements. To calculate the quarter use the following functions:

CEILING([MONTH NR] / 3)

Next you just have to select the last three months and the last two quarters. Do this in combination with year, otherwise you'll have problems if the selection will span over two years.

Hope this helps. Otherwise post more detailed information on where you're getting stuck.

[Feanor]

Also here's a helpfull calculation:

([YEAR]*100) + [MONTH NR]

This will result in something like this:
(2009 * 100) + 9  = 200909
(2009 * 100) + 10 = 200910
(2009 * 100) + 11 = 200911
(2009 * 100) + 12 = 200912
(2010 * 100) + 1  = 201001

Now you can sort your month numbers so you can easily select the last number (or first) of months, even if this wil span over multiple years. You can use the same logics for selecting the last number of quarters.