I have been asked to create a function which works out the previous working day.
The logic for this function is as follows:
If the current day is a sunday or Monday then pick the previous friday
Else pick the previous day.
I was then able to get the current day using DatePart(weekday,{CurrentDate}) and create the following function:
CREATE FUNCTION [dbo].[FUN_Previous_Working_Day] (@CurrentDate DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @PreviousDate DATETIME
DECLARE @Day INT
SET @Day = DATEPART(weekday,@CurrentDate)IF @Day = 7
SET @PreviousDate = DATEADD(d,-2,@CurrentDate)
ELSE
IF @Day = 1
SET @PreviousDate = DATEADD(d,-3,@CurrentDate)
ELSE
SET @PreviousDate = DATEADD(d,-1,@CurrentDate)RETURN @PreviousDate
END
GO