September 12, 2013 at 10:35 am
We have always waited until the first of each month to start our month end processing. We would use code like the following to determine the prior months last day.
declare@runDatedatetime;
declare@evalDtdatetime;
set@runDate = convert(char(10),getdate(),101);
set@evalDt = convert(char(10),(dateadd(dd, - datepart(dd,@runDate) , @runDate)),101);
We have been asked by management for awhile now that they want us to start processing sooner. Like when the first falls on a Monday or Sunday to be able to start on Saturday. So we have thought about how to make this work with all of the processes that have the above calculation, something similar would be done to find the first day of the prior month. So we looked at the dates through the years and determined that the earliest date we could potentially need to start would be the 26th, we always have Thanksgiving and the day after off. So we started adding the following code to our processes.
set @rundate = (case
when datepart(dd, GETDATE()) >= 26 then dateadd(dd, 10, GETDATE())
else GETDATE()
end);
Now management is throwing us a new curve. For this coming November they want to cut off our month at 11/22/2013. And they want anything processed after that date to fall into December results. As you can see the logic we were adding isn't going to work for this situation.
So my thoughts went to setting up a table that we could update that would contain the start date for that month and an end date. So in the this situation I would have a start date for the month of November as 11/01/2013, the end date would be 11/22/2013, these all would go under the month end date of 11/30/2013. Then for December the start date for the month would be 11/23/2013 and the end date would be 12/31/2013, month end date for these would be 12/31/2013.
I'm curious to know if or how others handle their dates? Do you think I'm on the right track with using a table? Any issues you've run into?
Thanks,
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 12, 2013 at 10:39 am
Yeah you need a calendar table. Check out this article. It is great explanation of what they can do for you.
http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 12, 2013 at 3:07 pm
We do month end processing all the time (banking). So what I usually do is this:
declare @LastMonth datetime
declare @thisMonth datetime
set @ThisMonth = convert(datetime,convert(char(6),getdate(),112) +'01')
set @LastMonth = dateadd(month,-1,@ThisMonth)
then all my data summaries and roll-ups involving last months data include
these variables in the where clause to bracket all last months data:
where date >= @LastMonth and Date < @ThisMonth
Also, I can run the queries anytime or any day after midnight on the first of any month and get my data.
edit: after typing this I think misread your question. However, handle it in a similar manner except that I'd set @ThisMonth to be the current date, and make @LastMonth be the first of the month. Sorry for misunderstanding your question. You would just need to calendar table to determine if you were past the last business day of the month as the previous poster mentioned and compare against that value.
The probability of survival is inversely proportional to the angle of arrival.
September 16, 2013 at 10:50 am
Thanks for the info Sean, I thought a table would be the best way to go. I don't think this example with completely work but I've got the general idea.
If I was to set the 'month end date' as a key to the table. What would be faster, setting the 'key' field as a datetime or an int? So either the date field is normal '09/16/2013' or as an int 20130916.
Int at first seems to be the best way but then you have to convert your other date field to int which could be slower.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 16, 2013 at 12:03 pm
below86 (9/16/2013)
Thanks for the info Sean, I thought a table would be the best way to go. I don't think this example with completely work but I've got the general idea.If I was to set the 'month end date' as a key to the table. What would be faster, setting the 'key' field as a datetime or an int? So either the date field is normal '09/16/2013' or as an int 20130916.
Int at first seems to be the best way but then you have to convert your other date field to int which could be slower.
If you are dealing with dates it would always be the best choice to use a date or datetime datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply