July 18, 2005 at 2:54 pm
Is there an easy method using SQL Server (SQL 2000 SP 3) to set up a DTS Process that executes only when the PreviousBusinessDate is equal to the last business day of the month? I was thinking about 1) Creating a WorkDate Table which contains all work days excluding holidays and weekends, 2) Creating a query that compares the PreviousBusiness Day's month to the current day's month and if they are equal then uses the PreviousBusiness Day's year and month to select the current month from the Work Date Table, then select max day for that month from the WorkDate Table.
Thanks in advance for any advice, examples, etc. Kevin
July 18, 2005 at 3:11 pm
/* Previous months last date */
SELECT DATEADD(DAY, DATEDIFF(DAY, DATEADD(MONTH, -1, GETDATE()), GETDATE()) - DATEPART(DAY, GETDATE()), DATEADD(MONTH, -1, GETDATE()))
/* Current Months last date */
SELECT DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())
If it is for the first day of the month at 12 AM. Just schdule it for 1 date and make the intervel monthly.
when the PreviousBusinessDate is equal to the last business day of the month => if the previous day is previous months last date why do you care whether it is business day or not. Your working table will have last business day where's if you check whether yesterday is last months last date or today is 1st day of the month that will satisfy your criteria I guess.
Regards,
gova
July 19, 2005 at 2:16 am
Couldn't you:
1. Check week day
2. If Monday to Thursday check month of next day
3. If Friday check month of monday
I do not know what kind of holidays that you/the customer celebrates, but the only relevant ones should be those that could occur in the last or first days of the month. Like for example new years eve and day in my case. Then run a special case before the main algorithm.
July 19, 2005 at 7:39 am
If you last business day. This will not consider holidays other that Saturday and Sunday.
/* Previous months last business date */
SELECT DATEADD(DAY, DATEDIFF(DAY, DATEADD(MONTH, -1, GETDATE()), GETDATE()) - DATEPART(DAY, GETDATE()) - CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SUNDAY' THEN -2
WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SATURDAY' THEN -1
ELSE 0 END, DATEADD(MONTH, -1, GETDATE()))
/* Current Months last business date */
SELECT DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE() - CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SUNDAY' THEN -2
WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SATURDAY' THEN -1
ELSE 0 END), GETDATE())
Regards,
gova
July 19, 2005 at 1:58 pm
Thanks for the queries. The following query is returning 7-2-2005 instead of 6-30-2005. I have to review it more in depth to see were the problem is.
/* Previous months last business date */
SELECT DATEADD(DAY, DATEDIFF(DAY, DATEADD(MONTH, -1, GETDATE()), GETDATE()) - DATEPART(DAY, GETDATE()) - CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SUNDAY' THEN -2
WHEN DATENAME(WEEKDAY, DATEADD(DAY, DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, 1, GETDATE())) - DATEPART(DAY, GETDATE()), GETDATE())) = 'SATURDAY' THEN -1
ELSE 0 END, DATEADD(MONTH, -1, GETDATE()))
July 19, 2005 at 3:28 pm
--What I have done is set up a table of business days:
CREATE TABLE BusinessDays (PK int IDENTITY(1, 1) primary key clustered, BusinessDate smalldatetime)
--Then I load it with the dates of every day for the next five years:
DECLARE @BusinessDate smalldatetime, @incr smallint
SET @BusinessDate = CAST('20050101' AS smalldatetime); SET @incr = 0
WHILE (@incr <= (365 * 5))
BEGIN
INSERT INTO BusinessDays (BusinessDate) VALUES (DATEADD(dd, @incr, @BusinessDate))
SET @incr = @incr + 1
END
-- now go back and delete the days you know are not business days; i.e. weekends, Christmas Day, New year's day, etc.
DELETE FROM BusinessDays WHERE
(DATEPART(dw, BusinessDate) IN (1, 7)) -- Sat and Sun
OR (DATEPART(m, BusinessDate) = 1 AND DATEPART(d, BusinessDate) = 1)
OR (DATEPART(m, BusinessDate) = 12 AND DATEPART(d, BusinessDate) = 25)
Finally, go back and edit by hand to remove the rest of the non-business days specific to your shop. Now you can check if a date is a business date with:
SELECT * FROM whereever WHERE WhatEverDate IN (SELECT BusinessDate FROM BusinessDays)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply