Thanks for the reply - i've posted the function below. It's called in a couple of ways;
1. Update statement within SSIS Package - UPDATE table1 SET field1 = dbo.udf_job_time(date1,date2,code)
2. Within views - select dbo.udf_job_time(date1,date2,code) as field1
Thanks in advance
_____
CREATE function [dbo].[udf_job_time] (@start_date datetime, @end_date datetime, @calendar_code varchar (10))
RETURNS numeric
AS
BEGIN
declare @table_counter int --used to popultate temp table with pseudo rowid
declare @start datetime --start time of job i.e. created time
declare @work_start datetime -- start of working day on the date the sr was created
declare @start_day varchar (10) --used to hold start day name for look up to calendar tables
declare @work_end datetime -- end of working day on the date the job completed
declare @end_day varchar (10) --used to hold end day name for look up to calendar tables
declare @cal_id varchar (10) -- used to hold the calendar id for use in look up to calendar tables
declare @end datetime --used to hold time engineer arrived on site to do work
declare @date_to_insert datetime --used to hold each day and increment them from first to last day
declare @time_taken numeric -- used to hold total time taken for return in function
declare @bad_date1 int --used to check if 1st date passed in is a valid date and if not go to end and return null
declare @bad_date2 int --used to check if 2nd date passed in is a valid date and if not go to end and return null
declare @days table (
all_dates datetime,
day_of_week varchar (10),
holiday varchar (1),
row int,
record_start_time datetime,
record_end_time datetime,
time_taken int) -- table variable to hold all calculated information
set @bad_date1 = isdate(@start_date)
if @bad_date1 = 0 goto error
set @bad_date2 = isdate(@end_date)
if @bad_date2 = 0 goto error
if @start_date >= @end_date
goto error
set @start = @start_date
set @end = @end_date
set @cal_id = @calendar_code
set @date_to_insert = @start
set @start_day = (select datename(weekday,@start))
set @work_start = (select convert(varchar,start_tm,108) from CALENDAR_WORKING_HOURS
where end_day = @start_day and SCHED_CAL_ID = @cal_id)
set @end_day = (select datename(weekday,@end))
set @work_end = (select convert(varchar,end_tm,108) from CALENDAR_WORKING_HOURS
where end_day = @end_day and SCHED_CAL_ID = @cal_id)
set @table_counter = 1
/* Populate table variable with all the days in the start - end range
with the name of the day (for lookup to calendar tables) and a pseudo rowid */
while convert(varchar,@start,112) <= convert(varchar,@end,112)
begin
insert into @days (all_dates,day_of_week,row)
values (@date_to_insert,datename(weekday,@date_to_insert),@table_counter)
set @date_to_insert = @date_to_insert + 1
set @table_counter = @table_counter + 1
IF convert(varchar,@date_to_insert,112) > convert(varchar,@end,112)
BREAK
ELSE
CONTINUE
END
/* Update the table variable to show which of the days in the table
are holidays - uses calebdar id variable for lookup to calendar tables*/
update @days
set holiday = (
select case
when substring(convert(varchar,all_dates,120),1,10) in (
select substring(convert(varchar,start_dt,120),1,10) from
calendar_hol_days a join
calendar_hol b
on a.excpt_cal_id = b.row_id
join calendar c
on b.row_id = c.excpt_cal_id
where c.row_id = @cal_id) then 'Y'
else 'N'
end)
/* Update table variable with the start and end times of days not at
beginning and end of date ranges - uses day name and calendar id
variable for look up to calendar tables */
Update @days
set record_start_time = (
select START_TM from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id),
record_end_time = (
select END_TM from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row > (select min(row) from @days)
and row < (select max(row) from @days)
/* Check to see if job start time is greater than the working day start time
for the first day and if so update table variable to show job start time
if not update table to show working day start time for first day */
if convert(varchar,@start,108) > convert(varchar,@work_start,108)-- compare time portions only
update @days set record_start_time = @start where row = (select min(row) from @days)
else
update @days set record_start_time = @work_start where row = (select min(row) from @days)
/* Update table variable to set first day end time to be the end of the working day*/
Update @days
set record_end_time = (select END_TM from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row = (select min(row) from @days)
/* If table variable has more than one row set last day start time
to be the start of the working day if only one row ignore update*/
If (select count (*) from @days) = 1
GOTO miss_step
else
Update @days
set record_start_time = (select start_tm from CALENDAR_WORKING_HOURS
where end_day = day_of_week AND SCHED_CAL_ID = @cal_id)
where row = (select max(row) from @days)
miss_step:
/* Update table variable to set last day end time to be the end of the job
unless the job finished after the end of the working day where end of working
day is used*/
if convert(varchar,@end,108) > convert(varchar,@work_end,108)-- compare time portions only
Update @days
set record_end_time = @work_end
where row = (select max(row) from @days)
else
Update @days
set record_end_time = @end
where row = (select max(row) from @days)
/* Update table variable to calculate time difference from start to end of
day for all days involved */
update @days
set time_taken =
datediff(minute,convert(varchar,record_start_time,108),convert(varchar,record_end_time,108))
/*set output variable to be the sum of all times where not a
holiday and time not null (weekends) */
set @time_taken = (select sum(time_taken) Job_Time
from @days
where holiday = 'N'
and time_taken is not null)
if @time_taken < 0
set @time_taken = 0
RETURN (@time_taken)
error:
RETURN (NULL)
END
