SQL UDF to return hours
-
I need a UDF that will do the following:
User: Will be sending me a datetime e.g '9 Nov 2004 15:00:00'
I want the UDF to do the following for me
Return in hours the difference between what the user has send and now (lets say now = '10 Nov 2004 11:00:00')
So the UDF will return to me 20.
But I dont want 20, I want the UDF to avoid any all hours which are not work related (any time after 16h00
until 8h00 in the morning), so I want this UDF to return 4. That means from '9 Nov 2004 15:00:00' I have calculated
1hr until 16h00 and 3hrs from 8 until 11h00 on '10 Nov 2004 11:00:00'
AGAIN IT MUST NOT CALCULATE WEEKENDS. Lets say '12 Nov 2004 15:00:00' was Friday and now = '15 Nov 2004 11:00:00', I must still
get 4 as a return from UDF
Preferably I will also need now to be getdate() (the system time) it looks like you can't use getdate() in UDF
Thanks in advanced
-
A possibility (gotta go in a few minutes!)
Calculating time diff A->B using 0800-1600 only.
Create a temporary table containing the following recordsA to 16:0008:00 to 16:00 for each day in between08:00 to B(modify slightly where A,B on same day, or A,B outside 0800-1600)Then sum differences.
-
I still don't understand what to do, I am a newbie when it comes to UDF
-
Try this
CREATE FUNCTION dbo.fn_CalculateHours
(@DateFrom datetime, @DateTo datetime)
RETURNS int
AS
BEGIN
DECLARE @hFrom int, @hTo int
SET @DateFrom = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,1,@DateFrom),120) + ' 08:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,2,@DateFrom),120) + ' 08:00:00'
ELSE @DateFrom
END)
SET @DateTo = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,-2,@DateTo),120) + ' 16:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,-1,@DateTo),120) + ' 16:00:00'
ELSE @DateTo
END)
SELECT @hFrom = DATEPART(hour,@DateFrom)
SELECT @hTo = DATEPART(hour,@DateTo)
RETURN ((DATEDIFF(day,@DateFrom,@DateTo) - (DATEDIFF(week,@DateFrom,@DateTo) * 2) - 1) * 8) +
(CASE WHEN @hFrom < 8 THEN 8
WHEN @hFrom > 16 THEN 0
ELSE 16 - @hFrom
END) +
(CASE WHEN @hTo < 8 THEN 0
WHEN @hTo > 16 THEN 8
ELSE @hTo - 8
END)
END
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks, David, you are a star
-
Hi Dave!
I am a newbie when it comes to SQL, sorry for inconvienience, your answer worked perfectly, but I meant to say minutes
With your above answer, can you change that to return MINUTES instead of hours and exclude holidays that are in TableA . Lets say TableA has
HolidayDate
2004-08-09 00:00:00.000
2004-09-24 00:00:00.000
2004-12-16 00:00:00.000
2004-12-26 00:00:00.000
2004-12-25 00:00:00.000
-
Try this
CREATE FUNCTION dbo.fn_CalculateMinutes
(@DateFrom datetime, @DateTo datetime)
RETURNS int
AS
BEGIN
DECLARE @hFrom int, @hTo int, @mFrom int, @mTo int, @hol int
SET @DateFrom = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,1,@DateFrom),120) + ' 08:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,2,@DateFrom),120) + ' 08:00:00'
ELSE @DateFrom
END)
SET @DateTo = (CASE DATEPART(weekday,@DateFrom)
WHEN 1 THEN CONVERT(varchar(10),DATEADD(day,-2,@DateTo),120) + ' 16:00:00'
WHEN 7 THEN CONVERT(varchar(10),DATEADD(day,-1,@DateTo),120) + ' 16:00:00'
ELSE @DateTo
END)
SELECT @hFrom = DATEPART(hour,@DateFrom)
SELECT @mFrom = (@hFrom * 60) + DATEPART(minute,@DateFrom)
SELECT @hTo = DATEPART(hour,@DateTo)
SELECT @mTo = (@hTo * 60) + DATEPART(minute,@DateTo)
SELECT @hol = ISNULL(SUM(
CASE
WHEN DATEDIFF(day,@DateFrom,HolidayDate) = 0 THEN 960 - @mFrom
WHEN DATEDIFF(day,HolidayDate,@DateTo) = 0 THEN @mTo - 480
ELSE 480
END),0)
FROM [TableA]
WHERE HolidayDate >= CONVERT(varchar(10),@DateFrom,120)
AND HolidayDate <= CONVERT(varchar(10),@DateTo,120)
RETURN ((DATEDIFF(day,@DateFrom,@DateTo) - (DATEDIFF(week,@DateFrom,@DateTo) * 2) - 1) * 480) +
(CASE WHEN @hFrom < 8 THEN 480
WHEN @hFrom > 16 THEN 0
ELSE 960 - @mFrom
END) +
(CASE WHEN @hTo < 8 THEN 0
WHEN @hTo > 16 THEN 480
ELSE @mTo - 480
END) -
@hol
END
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks once again Dave, where did you learn SQL or maybe how? Please give me an indication because I am relying mostly on help... -
Self taught, mostly by practice and looking at samples and experience. BOL and web search helps and looking at forums like this one. This forum has a lot of articles that contain useful tips, worth searching. You will find answers to most questions otherwise just post your question, there are a lot of people with a vast knowledge of sql who can help.
If you search previous posts, your question should appear with peoples suggestions for books that may help.
Have fun
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply