I had a request to show the calendar for the last 7 business days a part time employee worked. However, to ensure that I have the correct start date to show a 2 week calendar, I needed to omit any day the person was off. It's a strange request, but this is interesting.

GetBusinessDay.sql

-- Assume that we have a table that tracks the days off. We'll define it here
-- when actually we'd be pulling this from our connection.
declare @myDaysOff table
(
  dayOff datetime
)

-- Let's seed this table
insert into @myDaysOff (dayOff)
values ('2/20/2013'), ('2/27/2013'), ('3/2/2013'), ('3/3/2013')

-- These variables would likely get pushed via params
declare @currentDay date
set @currentDay = GetDate()		-- Using the current date 
declare @businessDayOffset int
set @businessDayOffset = 7		-- Let's grab the 7th business day ago

-- NOTE: If we moved this into a sproc, we'd be using the bottom as the body
-- These variables are being used internally
declare @tDate date				-- A variable to hold the "current" date
declare @tOffset int			-- A variable to hold the temporary offset
declare @tDaysOffCount int		-- A variable to hold the number of days off

-- Let's see these internal variables
set @tDate = @currentDay
set @tOffset = 0
set @tDaysOffCount = 0

-- Loop through the dates backwards
while (@tOffset < @businessDayOffset)
	begin
		select @tDaysOffCount = count(dayOff) from @myDaysOff where dayOff = @tDate
	
		if @tDaysOffCount = 0 
			set @tOffset = @tOffset + 1
			
		if @tOffset < @businessDayOffset
			set @tDate = DATEADD(d, -1, @tDate)
	end

-- Let's output the date
select @tDate