Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cplusplus
Aged Yak Warrior
567 Posts |
 Posted - 2012-11-25 : 05:16:41
|
| I am trying to create a sql procedure, this procedure will be used by ETL tool.procedure will extract data for one month, each time, if i ran the procedure for oct 2012.I Want to use between created_date >='10/01/2012' and created_date <='10/31/2012'How does the procedure increment to one month based on valuesmonth: oct yr= 2012Is it possible based on those two values can it increment to NovMonth= novyear=2012These two values i want to store in table, which ever it is curently running.On the next call it has to increment extacly by one month.Is it possible or may be any diff logic is better, please kindly provide any ideas to handle this date range increment by a month.Thank you very much for the helpful information. |
|
|
Elizabeth B. Darcy
Starting Member
39 Posts |
Posted - 2012-11-25 : 08:40:01
|
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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
How to dynamically increment by one month each tim