Changing a SQL Server function to work with multiple records -

i have function use compute number of days patient unavailable in order reduce number of days have breached waiting times deadline.

the function follows:

create function [dbo].[getdaysunavailable]  (     @intref     int ) returns int   begin  declare @returnvalue        int declare @dtunavailfrom  datetime declare @dtunavailto        datetime declare @dtrefrcvd      datetime  select @dtunavailfrom = unavailablefrom referrals referralid = @intref select @dtunavailto = unavailableto referrals referralid = @intref select @dtrefrcvd = referralreceiveddate referrals referralid = @intref  if isnull(@dtunavailfrom,'') = '' or isnull(@dtunavailto,'') = ''   begin     select @returnvalue = 0   end else   begin     if @dtrefrcvd > @dtunavailfrom       begin         select @returnvalue = datediff(day,@dtrefrcvd, @dtunavailto)       end     else       begin         select @returnvalue = datediff(day,@dtunavailfrom, @dtunavailto)       end     end  return @returnvalue end 

as can see works out difference between date person unavailable , either date unavailable or date referral received. fine using original structure whereby 2 date fields in main table. problem there ever 1 period of unavailability.

i have changed table structure unavailability periods in own table has 1-to-many relationship main table.

but can't figure out how function cope withe new structure. need able pull every row in unavailability table relates parent record , them loop through each row, summing unavailability , returning total @ end.

any ideas?

you can return value subquery

select sum(datediff(day,unavailablefrom,unavailableto)) summary unavailabilitytbl referralid = @intref group referralid