June 26, 2013 at 10:05 am
I have a stored procedure that is used to sync dates between two databases. It looks at the project start date in one database and the production date for a specific component in the other database. If that production date is later than the project start date, it syncs the two dates.
The stored procedure is run at midnight each night. Component production is set at 2:30pm each day. So, for example, at 2:30pm on July 1st, the production for July 2nd is set. Thus it doesn't do any good to have the stored procedure set production dates to July 1st or July 2nd and instead should use July 3rd as the next available start date.
This issue is addressed by checking if the intended start date is at least one day in the future and, if not, changing the intended start date to be such.
What I'm not entirely sure how to go about doing is checking to ensure that the new intended start date isn't a Saturday, Sunday or Holiday.
Checking if the date is a Saturday or Sunday is easy enough using DATENAME(dw, @STARTDATE) and then adding 1 or 2 days to the date accordingly.
What I'm not entirely sure about is the proper way to continue adding days to the date until it's neither a Saturday no Sunday and also doesn't appear in the Holidays table.
What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?
June 26, 2013 at 10:36 am
skempf (6/26/2013)
I have a stored procedure that is used to sync dates between two databases. It looks at the project start date in one database and the production date for a specific component in the other database. If that production date is later than the project start date, it syncs the two dates.The stored procedure is run at midnight each night. Component production is set at 2:30pm each day. So, for example, at 2:30pm on July 1st, the production for July 2nd is set. Thus it doesn't do any good to have the stored procedure set production dates to July 1st or July 2nd and instead should use July 3rd as the next available start date.
This issue is addressed by checking if the intended start date is at least one day in the future and, if not, changing the intended start date to be such.
What I'm not entirely sure how to go about doing is checking to ensure that the new intended start date isn't a Saturday, Sunday or Holiday.
Checking if the date is a Saturday or Sunday is easy enough using DATENAME(dw, @STARTDATE) and then adding 1 or 2 days to the date accordingly.
What I'm not entirely sure about is the proper way to continue adding days to the date until it's neither a Saturday no Sunday and also doesn't appear in the Holidays table.
What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?
You should search this site for calendar tables. There are several great articles for this type of thing.
What would be the proper structure of a loop to ensure the date is neither a weekend nor a holiday?
There is no proper structure for a loop for this type of thing. A loop is not needed if you use a calendar table and set based queries.
_______________________________________________________________
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/
June 26, 2013 at 10:36 am
I'd build a calendar tables (there are articles on this site for that). essentially populate it with all days, mark weekends, holidays, and other days. That way you can join with that table and determine the next available "work" day.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply