Q:

SQL working days function

Im trying to work out the previous 5 working days, if its a Saturday or sunday then it needs to use Friday as the last working day.
Ive got the if else part of this query working but when I try to use this to create a function im getting error messages, any ideas what ive missed?
create table holidays  (
  date date);
GO

create function dbo.WorkDays
(

@date datetime, 
@days int

)

returns date 

as

Begin

IF datename(dw,@date) = 'Saturday' 

select dateadd(dd,-1,thedate)
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

Else If datename(dw,@date) = 'Sunday'

select dateadd(dd,-1,thedate)
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

Else

select thedate
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

End 

drop table holidays

A:

Error showed is:
Select statements included within a function cannot return data to a client.
You need to declare a @return variable, and assign it the output of each select statement:
create function dbo.WorkDays
(

@date datetime, 
@days int

)

returns date 

as

Begin
DECLARE @return date

IF datename(dw,@date) = 'Saturday' 

select @return = dateadd(dd,-1,thedate)
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

Else If datename(dw,@date) = 'Sunday'

select @return = dateadd(dd,-1,thedate)
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

Else

select @return= thedate
  from (
  select thedate=dateadd(d,-v.day,cast(@date as date)),
         rn=row_number() over (order by v.day),
         weekday = datename(dw,dateadd(d,-v.day,cast(@date as date)))
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))v(day)
  left join holidays h on h.date = dateadd(d,v.day,cast(@date as date))
  where left(datename(dw,dateadd(d,-v.day,cast(@date as date))),1) <> 'S'
  ) x
  where @days = rn

  return @return
End 

I suppose the logic of your queries is correct.

