Click here to Skip to main content
15,890,579 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
MS SQL SERVER 2008


How do I call the function Fn_workinghour

Please assist


SQL
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



---------------------------------------------------------------------------
-----------------------------------------------------------------------------


SQL
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
Posted
Updated 31-Oct-17 5:50am
v2
Comments
Bryian Tan 31-Oct-17 11:36am    
What wrong with what you have now? It look fine to me. Any error message?
Member 12770648 31-Oct-17 12:23pm    
How do you call the called subroutine from the calling?
Karthik_Mahalingam 31-Oct-17 22:50pm    
use  Reply   button to post comments/query to the concerned user, so that the user gets notified and respond to your text.

1 solution

The SQL queries look fine and it should return the total hours for each row in the @table. You didn't mention what was the issue. I'm guessing the query is throwing the error "Invalid object name 'F_table_date'."?

If that the case, if you look closely, the Fn_workinghour function is referring to another table value function, namely "F_table_date". You need to run a SQL script to create that function in your database environment. Here is the script F_TABLE_DATE.sql
[^] .

Hopefully, that the issue.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900