A few thoughts that I have are as follows:
1. Don't store the month and year values as you indicated (Oct, 2012). Store them as real dates. For example, store them in a table with a column that is of date type and store the first day of the month of interest. (20121001 for Oct 2012).
2. Prefer using something like create_date >= '10/1/2012' and create_date < '11/1/2012' over what you proposed (created_date >='10/01/2012' and created_date <='10/31/2012').
3. If you store the dates like I said in my bullet point 1 above, then computing the beginning and end dates is simple:create date >= datefromTable and create_date < dateadd(mm,1,datefromTable)
4. What you said about "On the next call it has to increment extacly by one month" - it would be better to advance that date from the ETL tool rather than having SQL remember what the last call was and then increment it. Error recovery, avoiding incorrect data in a multi-user environment etc. being my reasons for suggesting so. That is assuming that you are making a call for each month from the ETL tool. If you are simply getting the data for all the months in one call from the ETL tool, then of course, it is better to do the date progression in SQL
________________________________________
-- Yes, I am indeed a fictional character.
How to dynamically increment by one month each tim
Posted - 11/25/2012 : 05:16:41
