September 14, 2005 at 2:02 pm
This is my resolution to a question I posted back on August 30, 2005.
What is a quick and easy way to calculate the last working day of the previous month using a SQL Statement. (We are using SQL Server 2000, SP 3).
Resolution:
The following SQL Statement is what I developed to calculate the last working day of the previous month. I did use some of the suggestions I received. The key was to get the previous business date from an existing data table (MellonTrans_Temp), use it to determine the previous month and year, and then use the previous month and year to pull the last working date (of that month and year) from an existing Dates Table. Please let me know what you think and if my solution would work in all situations (i.e., If the month is January, leap year, etc.)
DECLARE @PrevBusinessDate1 AS varchar(10)
DECLARE @PrevBusinessDate1_Month AS varchar(2)
DECLARE @PrevBusinessDate1_Year AS smallint
/* Get Max Price Date (previous business date) From Mellon Table.*/
DECLARE @PriceDate AS varchar(10)
SET @PriceDate = (
SELECT
CONVERT(char(12), Max(PriceDate), 101)
FROM MellonTrans_Temp
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
)
/*Set @PrevBusinessDate1 to Max Price Date on Mellon Table.*/
SET @PrevBusinessDate1 = @PriceDate
SET @PrevBusinessDate1_Month = MONTH (DATEADD (mm, -1, @PrevBusinessDate1))
/*Determine @PrevBusinessDate1_Year using @PrevBusinessDate1_Month and the Year in @PrevBusinessDate1. If @PrevBusinessDate1_Month is 1 then subtract 1 from the Year.*/
SELECT @PrevBusinessDate1_Year = (
SELECT NewDate =
CASE
/*Compare @PrevBusinessDate1_Month to 12 because 1 has already been subtracted. If @PrevBusinessDate1_Month 12 then subtract 1 from Year. */
WHEN @PrevBusinessDate1_Month = 12 THEN YEAR (DATEADD (yy, -1, @PrevBusinessDate1))
ELSE YEAR (@PrevBusinessDate1)
END
)
/* Select the PrevMonthLastCalendarDate (Max(Date)) from the Dates Table where MONTH(Date) = @PrevBusinessDate1_Month AND YEAR(Date) = @PrevBusinessDate1_Year, and Date is not a Weekend or Holiday Date. */
DECLARE @PrevMonthLastCalendarDate AS DateTime
SET @PrevMonthLastCalendarDate = (
SELECT MAX(Date) AS PrevMonthLastCalendarDate FROM FISQL.DBO.Dates
WHERE MONTH(Date) = @PrevBusinessDate1_Month
AND YEAR(Date) = @PrevBusinessDate1_Year
AND DATEPART(dw, Date) NOT IN (6, 7) /* Eliminate Weekend Dates */
AND Date NOT IN (SELECT Holiday_Date FROM Holidays)
)
SELECT @PrevMonthLastCalendarDate
September 14, 2005 at 2:37 pm
I think this will work too (Holidays Not Considered)
DECLARE @LastWorkingDayOfPreMonth VARCHAR(12)
DECLARE @DateToCheck DATETIME
SET @DateToCheck = '05/10/2005'
SELECT @LastWorkingDayOfPreMonth = CONVERT(VARCHAR, DATEADD(DAY, 0 - ((DATEPART(DAY, @DateToCheck)) +
CASE WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @DateToCheck)), @DateToCheck)) = 'SUNDAY' THEN 2
WHEN DATENAME(WEEKDAY, DATEADD(DAY, 0 - (DATEPART(DAY, @DateToCheck)), @DateToCheck)) = 'SATURDAY' THEN 1
ELSE 0 END
), @DateToCheck), 101)
SELECT @LastWorkingDayOfPreMonth
Regards,
gova
September 14, 2005 at 2:46 pm
A calendar table may not look as sexy as a function but is so flexible that is usually the method that I tend to recomend for this kind of things!
Cheers!
* Noel
September 15, 2005 at 11:53 am
What DATEFIRST setting do you have?
with default which is 7 the condition :
AND DATEPART(dw, Date) NOT IN (6, 7) /* Eliminate Weekend Dates */
is wrong and should be "NOT IN (1, 7) "
Leonid
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply