August 13, 2014 at 2:46 am
Hi,
i midst to searching for this function
create function overlaphours(@s1 as datetime,@f1 as datetime,@s2 as datetime,@f2 as datetime) returns integer as
begin
declare @result as int
set @result=0
if @s2>=@s1 and @f2<=@f1
set @result=datediff(hh,@s2,@f2)
else if @s2 =@s1 and @f2<=@f1
set @result=datediff(hh,@s1,@f2)
else if @s2>=@s1
set @result=datediff(hh,@s2,@f1)
else if @s2 =@f1
set @result=datediff(hh,@s1,@f1)
return @result
end
go
How to use this function ?
Can help?
August 13, 2014 at 3:34 am
Have a go at this:
create function overlaphours(@s1 as datetime,@f1 as datetime,@s2 as datetime,@f2 as datetime) returns integer as
begin
declare @result as int
set @result=0
if @s2>=@s1 and @f2<=@f1
set @result=datediff(hh,@s2,@f2)
else if @s2<@s1 and @f2>=@s1 and @f2<=@f1
set @result=datediff(hh,@s1,@f2)
else if @s2>=@s1 and @s2<=@f1 and @f2>@f1
set @result=datediff(hh,@s2,@f1)
else if @s2<=@s1 and @f2>=@f1
set @result=datediff(hh,@s1,@f1)
return @result
end
go
Then use this sample to test the different cases:
select dbo.overlaphours('2014-08-13 07:00','2014-08-13 09:00','2014-08-13 08:00','2014-08-13 10:00')
August 13, 2014 at 4:14 am
What's wrong with a set-based solution?SELECTCASE
WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))
ELSE 0
END
FROM(
VALUES(@s1, @f1),
(@s2, @f2)
) AS d(FromTime, ToTime);You can wrap this as an ITVF.
N 56°04'39.16"
E 12°55'05.25"
August 13, 2014 at 5:03 am
Very neat idea this Max(From), Min(To).
But when I try this on a scalar function it runs slower than my scalar function.
Don't know why, but I don't use table valued function an awful lot.
August 13, 2014 at 5:09 am
Did you wrap the code as an inline table-valued function?
CREATE FUNCTION dbo.OverlapHours
(
@s1 DATETIME,
@f1 DATETIME,
@s2 DATETIME,
@f2 DATETIME
)
RETURNS TABLE
AS
RETURN(
SELECTCASE
WHEN MAX(FromTime) < MIN(ToTime) THEN DATEDIFF(HOUR, MAX(FromTime), MIN(ToTime))
ELSE 0
END AS [Hours]
FROM(
VALUES(@s1, @f1),
(@s2, @f2)
) AS d(FromTime, ToTime)
);
N 56°04'39.16"
E 12°55'05.25"
August 13, 2014 at 5:18 am
Show me an example given a table with 4 dates.
It should return the 4 dates and the result.
Have never used this ITVF technique.
August 13, 2014 at 5:28 am
DECLARE@Sample TABLE
(
s1 DATETIME,
f1 DATETIME,
s2 DATETIME,
f2 DATETIME
);
INSERT@Sample
(
s1,
f1,
s2,
f2
)
VALUES('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 10:00', '2014-08-13 19:00'),
('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 09:00', '2014-08-13 10:00'),
('2014-08-13 07:00', '2014-08-13 12:00', '2014-08-13 13:00', '2014-08-13 19:00');
-- SwePeso
SELECTs.*,
x.*
FROM@Sample AS s
CROSS APPLYdbo.OverlapHours(s1, f1, s2, f2) AS x;
N 56°04'39.16"
E 12°55'05.25"
August 13, 2014 at 6:15 am
Yes it's a lot faster. Thus on half a million rows
3243 milliseconds using my scalar function
3822 milliseconds using scalar function which applies your min/max concept (thus slightly less code)
593 milliseconds using your ITVF
To me it looks like a lot of work to transform 1 row of 4 columns into 2 rows of 2 columns and apply the min/max functions. Yet it does it very quickly.
Nevertheless, this CROSS APPLY stuff has never entered my blood stream.
But there is always a first time for everything.
I guess the trick is to stop and think whenever you use a scalar-valued function to ask youself if it can be done with a table-valued function - faster.
August 13, 2014 at 6:37 am
Thank you for taking the time (pun intended) to test the different solutions.
N 56°04'39.16"
E 12°55'05.25"
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply