MS SQL SERVER 2008
How do I call the function Fn_workinghour
Please assist
DECLARE @table AS TABLE(
startday DATETIME,
endday DATETIME)
INSERT INTO @table
SELECT '22-Jun-2010 13:00:00', '22-Jun-2010 13:15:00'
UNION ALL SELECT '22-Jun-2007 13:00:00', '23-Feb-2010 13:15:00'
UNION ALL SELECT '21-Jun-2010 13:00:00', '23-Jun-2010 13:00:00'
SELECT dbo.fn_workinghour(startday, endday) FROM @table
---------------------------------------------------------------------------
-----------------------------------------------------------------------------
alter FUNCTION Fn_workinghour (@start_date DATETIME,
@end_date DATETIME)
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @work_calender AS TABLE (
[day_number] [VARCHAR] (50),
[day_name] [VARCHAR] (50),
[begin_time] [DATETIME],
[end_time] [DATETIME],
[duration] [REAL] )
INSERT INTO @work_calender
SELECT 1,
'Monday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 2,
'Tuesday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 3,
'Wednesday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 4,
'Thursday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 5,
'Friday',
'7:00:00 AM',
'6:00:00 PM',
39600
UNION ALL
SELECT 6,
'Saturday',
'8:00:00 AM',
'1:00:00 PM',
18000
UNION ALL
SELECT 7,
'Sunday',
'12:00:00 AM',
'12:00:00 AM',
0
DECLARE @total_hours DECIMAL(10, 2)
SELECT @total_hours = SUM(CASE
WHEN Dateadd(DAY, Datediff(DAY, 0, @start_date
),
0)
=
Dateadd(DAY, Datediff(DAY, 0,
@end_date), 0) THEN
Datediff(SECOND, @start_date, @end_date)
WHEN [DATE] = Dateadd(DAY, Datediff(DAY, 0,
@start_date), 0
) THEN
CASE
WHEN
@start_date > [DATE] + begin_time THEN
Datediff(SECOND, @start_date, [DATE] +
end_time)
ELSE duration
END
WHEN [DATE] = Dateadd(DAY, Datediff(DAY, 0,
@end_date), 0)
THEN
CASE
WHEN
@end_date < [DATE] + end_time THEN
Datediff(SECOND, [DATE] + begin_time,
@end_date)
ELSE duration
END
ELSE duration
END) / 60.0 / 60.0
FROM F_table_date(@start_date, @end_date) d
INNER JOIN @work_calender c
ON d.weekday_name_long = c.day_name
RETURN @total_hours
END
What I have tried:
This is a program under construction