asked on

SQL Update Query Help

I have a calendar table with CalendarDays(dates in chronological order), OfficeWorkdays (specifices if that day is a workday, closure, weekend, holiday) ad a DoorsDue date that I need a value in based on a variable. The variable being passed will either be a positive or negative number.

DoorsDueDate needs to equal (CalendarDays +/- variable) where OfficeWorkdays = 'workday'

For example, if CalendarDays = 7/2/13 and the variable is '-2' the correct value for that date is '6/28/13' because 6/29 and 6/30 are OfficeWorkdays = 'Weekend'. I want to add/subtract the variable but only using calendardays that are workdays.

Thanks!

plusone3055

solution is a little long but it does work :)

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/8af3ea0b-a4b2-4bd8-a3c8-15963fbd9564/subtracting-business-days

an even more extreme solution with holidays
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/6ac4543f-22b5-419f-87db-ea747ec80500/datediff-calculating-working-days

Dulton

DECLARE @Var INT
, @CalDate DATE
, @NewDate DATE

SELECT @Var = -2
,@CalDate = '2013-07-02'

SET @NewDate = DateAdd(Day,@Var,@CalDate)

/*
If day of week is 1 or 7, (sunday or Monday), remove another 2 days for the weekend so it acts as if they are not there
*/
If DatePart(Weekday,@NewDate) IN (1,7)
BEGIN

SET @NewDate = DateAdd(Day,-2,@NewDate)

SET @

END

Norm-al

ASKER

I don't need to figure out 'standard' work days' this is a field in the calendar table.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Anthony Perkins

This is how I tested it:

DECLARE @CalendarDays TABLE (OfficeWorkDays date NOT null)
INSERT @CalendarDays(OfficeWorkDays)
VALUES ('20130626'), ('20130627'), ('20130628'), ('20130701'), ('20130702'), ('20130703'), ('20130705'), ('20130708')

DECLARE @DoorsDueDate smallint = -2,
	@CalendarDay date = '20130702'

;WITH CalendarCTE AS (
	SELECT	OfficeWorkDays, 
		ROW_NUMBER() OVER (ORDER BY OfficeWorkDays DESC) Row
	FROM @CalendarDays
	WHERE	OfficeWorkDays < @CalendarDay
	)
SELECT OfficeWorkDays
FROM CalendarCTE
WHERE Row + @DoorsDueDate = 0

Open in new window