January 28, 2015 at 11:16 am
Hi all,
I have made the following Scalar-valued function:
CREATE FUNCTION [dbo].[TimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER -- code 2: 1/10, code 4: 1/100
)
RETURNS NVARCHAR(8)
AS
BEGIN
DECLARE
@ClearedTime AS INTEGER
, @TimeInMins AS INTEGER
, @RemainingSeconds AS INTEGER
SET @ClearedTime = @OriginalTime / 10
SET @TimeInMins = FLOOR(@ClearedTime/6000)
SET @RemainingSeconds = @ClearedTime - (@TimeInMins * 6000)
RETURN CASE
WHEN @TimeInMins = 0 AND LEN(@RemainingSeconds) = 4THEN CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 2)) + '.' + CONVERT(NVARCHAR(8),RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins > 0 AND LEN(@RemainingSeconds) = 4 THEN CONVERT(NVARCHAR(8),@TimeInMins) + '.' +CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 2)) + '.' + CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 2))
END
END
What it does is convert numbers to times
eg: 81230 gets divided by 10 (times in seconds: 8123). This 1 1 full minute, and the remainder = 2123 making it 1.21.23 mins)
So far so good (function works perfectly)
My question: sometimes times are in 1/100 (like above sample), sometimes in 1/10.
This means that, eg. with a time like 3.23.40 the last zero must be deleted.
My thoughts are to use the results from the Return Case part, and as the code = 4: leave it as it is,
is the code 2 the use LEFT(... result Return Case ..., Len(..result Return Case.. - 1))
I am struggling to get this working, what to do
Thanks in advance
Hein
PS
There are 5 codes: 0 1 2 3 and 4
January 28, 2015 at 11:44 am
Not a fan of this function as you might get performance problems, but maybe this is what you need.
RETURN CASE WHEN @TimeInMins = 0
THEN CONVERT(VARCHAR(2), LEFT(@RemainingSeconds, 2)) + '.' ELSE '' END
+ CONVERT(VARCHAR(2),RIGHT(@RemainingSeconds, LEN(@RemainingSeconds) - 2))
January 28, 2015 at 12:11 pm
Thanks You Luis for your response.
You suggestion would mean that I have to add a line for each of the 5 codes there is.
At the moment I have 10 lines in theReturn Case section, this would grow to 50! (Not good for performance!)
I agree that my function is somewhat lengthy, adding all these lines would make it even worse.
Is it possible to use the outcome of the first return case (e.g. 1.3.4.50) and loop through a 2nd return case
EG:
Return Case Two
WHEN @code = 0 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 1 THEN result = result (meaning: nothing has to be done)
WHEN @code = 2 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 3 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 4 THEN result = result (meaning: nothing has to be done)
end
Performancewise this would be (I think) the fastest way.
Hein
January 28, 2015 at 12:29 pm
I can't help you further if I only have a part of what you're doing.
January 28, 2015 at 12:50 pm
The complete function is as follows (but basically what you already know):
CREATE FUNCTION [dbo].[fnTimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER (code 0: 1/100, code 1: 1/10, code 2: 1/10, code 3: 1/10, code 4: 1/100)
)
RETURNS NVARCHAR(8)
AS
BEGIN
DECLARE
@ClearedTime AS INTEGER
, @TimeInMins AS INTEGER
, @RemainingSeconds AS INTEGER
SET @ClearedTime = @OriginalTime / 10
SET @TimeInMins = FLOOR(@ClearedTime/6000)
SET @RemainingSeconds = @ClearedTime - (@TimeInMins * 6000)
RETURN CASE
WHEN @OriginalTime = 0THEN ''
WHEN @TimeInMins = 0 AND LEN(@RemainingSeconds) = 1THEN '00.0'+ CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 1))
WHEN @TimeInMins = 0 AND LEN(@RemainingSeconds) = 2THEN '00.' + CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins = 0 AND LEN(@RemainingSeconds) = 3THEN '0' + CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 1)) + '.' + CONVERT(NVARCHAR(8),RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins = 0 AND LEN(@RemainingSeconds) = 4THEN CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 2)) + '.' + CONVERT(NVARCHAR(8),RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins > 0 AND LEN(@RemainingSeconds) = 1 THEN CONVERT(NVARCHAR(8),@TimeInMins) + '.' + '00.0' + CONVERT(NVARCHAR(8), @RemainingSeconds)
WHEN @TimeInMins > 0 AND LEN(@RemainingSeconds) = 2 THEN CONVERT(NVARCHAR(8),@TimeInMins) + '.' + '00.' +CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins > 0 AND LEN(@RemainingSeconds) = 3 THEN CONVERT(NVARCHAR(8),@TimeInMins) + '.' + '0' + CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 1)) + '.' + CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 2))
WHEN @TimeInMins > 0 AND LEN(@RemainingSeconds) = 4 THEN CONVERT(NVARCHAR(8),@TimeInMins) + '.' +CONVERT(NVARCHAR(8), LEFT(@RemainingSeconds, 2)) + '.' + CONVERT(NVARCHAR(8), RIGHT(@RemainingSeconds, 2))
END
END
Possible times that are used as input
38700
38710
38740
123450
328800
etc.
The last zero (0) is redundant, so I delete this using @ClearedTime = @OriginalTime / 10
Then I check if there are miniutes in the time
Then I check how many seconds and 1/100 of a second a remaining
The results of this I use in the Return Case.
Because a time can be 1.00.00 or 1.00.01 some extra calculations are needed because if the time, without the minutes = 0001 all 0 will "fall away"
The results of these calculations are times like 38.23, 40.20, 1.20.63 and 12.58.90
Because time comes in 2 variants: in 1/10 and 1/100 of a second an additional, final, step has to be made: getting rid of the last 0 if the code is 0 or 4.
I can do this in the query but for complexity reason I would like to do it in the function itself.
Therefor my thoughts to tackle this problem are:
Return Case "Two"
WHEN @code = 0 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 1 THEN result = result (meaning: nothing has to be done)
WHEN @code = 2 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 3 THEN result = LEFT(result 1st case, len(result 1st case - 1)
WHEN @code = 4 THEN result = result (meaning: nothing has to be done)
end
I have tried a number of things, but none of them work
Can you work with this information?
Hein
January 28, 2015 at 1:38 pm
Are you aware that using a scalar UDF does or can do the following:
1) void the use of parallelism, leading to disastrously bad performance and potentially concurrency issues
2) prevents accurate estimates, leading to disastrously bad performance and potentially concurrency issues
3) leads to row-by-row execution under the covers, leading to, yes, disastrously bad performance and potentially concurrency issues
Get a copy of the SQL Server MVP Deep Dive 2 book and read my chapter entitled "Death by UDF" to learn more about how bad they are.
AVOID SCALAR UDFs IF AT ALL POSSILBE!!!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 28, 2015 at 1:46 pm
Hi Kevin
I wasn't aware of this, thanks for the warning!
I am a beginner in SQL Server, and am trying to find my way, doing as much new stuff SQL Serveras possible.
I will get a copy of the book you're describing
Hopefully this will guide me to do the better things better !
Thanks again
Hein
January 28, 2015 at 2:08 pm
I'm not sure that I understand the second parameter. This is what I've made.
Note that I changed the scalar function into an InLine Table-valued function. To know why, read the following article: http://www.sqlservercentral.com/articles/T-SQL/91724/
ALTER FUNCTION [dbo].[tfnTimeCalc]
(
@OriginalTime AS INTEGER
, @TenthsOrHundredths AS INTEGER --(code 0: 1/100, code 1: 1/10, code 2: 1/10, code 3: 1/10, code 4: 1/100)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH Timeparts AS(
SELECT @OriginalTime / 60000 TimeInMins,
(@OriginalTime % 60000) / 10 RemainingSeconds
)
SELECT CASE WHEN TimeInMins > 0
THEN CONVERT(VARCHAR(8),TimeInMins) + '.' ELSE '' END
+ SUBSTRING(CAST( 100 + (RemainingSeconds / 100.) AS varchar(20)), 2, CASE WHEN @TenthsOrHundredths IN(1,4) THEN 5 ELSE 4 END) ftime
FROM Timeparts
GO
DECLARE @test-2 TABLE( myInt int)
INSERT INTO @test-2 VALUES(38700),(38710),(38740),(123450),(328800)
SELECT *
FROM @test-2
CROSS APPLY tfnTimeCalc(myInt, 1) f
CROSS APPLY tfnTimeCalc(myInt, 0) f2
Are my assumptions correct? Or did you expect different results?
January 29, 2015 at 12:02 pm
Luis, Sorry for not replying to your thread yesterday.
Timedifferences between the Netherlands and Mexico ...
Anyway: the results of your code looks to be exactly what I am looking for.
I hope you can help me a bit further because Im now swimming in waters I have never been before. I will dig in this subject and study it later
First of all: how do I call this function within a query: I tried [dbo].[tfnTimeCalc](crtFinalTime, crtETMode) but this doesn't work
Note: crtFinalTime is the time I want to recalculate, crtETMode is the field that holds the values for the accuracy, ranging from 0 to 4
When I created the function I have left out the part that starts with "DECLARE @test-2 TABLE( myInt int)". I guess that is allright?
Also: times can be of varying length: e.g.: 38000 for 500 meters, 138000 for 1000, 1500 etc and 1234560 for 5000 and 10000mtrs
As I read you code only lengths of 4 and 5 characters are used (correct?)
Anyway: I am glad you've put me on the right track
Hein
January 29, 2015 at 12:28 pm
SpeedSkaterFan (1/29/2015)
Luis, Sorry for not replying to your thread yesterday.Timedifferences between the Netherlands and Mexico ...
That's totally understandable, don't worry.
SpeedSkaterFan (1/29/2015)
Anyway: the results of your code looks to be exactly what I am looking for.I hope you can help me a bit further because Im now swimming in waters I have never been before. I will dig in this subject and study it later
First of all: how do I call this function within a query: I tried [dbo].[tfnTimeCalc])crtFinalTime, crtETMode) but this doesn't work
Note: crtFinalTime is the time I want to recalculate, crtETMode is the field that holds the values for the accuracy, ranging from 0 to 4
When I created the function I have left out the part that starts with "DECLARE @test-2 TABLE( myInt int)". I guess that is allright?
The "DECLARE @test-2 TABLE" part was to have something to show the results. The function returns a table and because it depends on values from a table, you need to use the APPLY operator. You can read about it on Books On Line or in these articles by Paul White.
Understanding and Using APPLY (Part 1)[/url]
Understanding and Using APPLY (Part 2)[/url]
The article that I included in my previous post shows examples as well and demonstrates how these functions don't suffer from the same problems as scalar functions. They're not perfect but they're a great option.
SpeedSkaterFan (1/29/2015)
Also: times can be of varying length: e.g.: 38000 for 500 meters, 138000 for 1000, 1500 etc and 1234560 for 5000 and 10000mtrsAs I read you code only lengths of 4 and 5 characters are used (correct?)
The function will work with any length and will show either 1 or 2 decimals.
SpeedSkaterFan (1/29/2015)
Anyway: I am glad you've put me on the right track
Hein
I'm glad that you found an opportunity to learn and I could help you.
If you have any more questions, feel free to ask them.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply