April 17, 2009 at 1:27 am
I use a "when then else" in my sql to change a negative value to zero.
In this sql I always have to repeat the essential part of the sql, e.g.
declare @aFrom int set @aFrom = 30
declare @aRatio int set @aRatio = 20
declare @aCount int set @aCount = 29 -- 0 , 30, 31, 49, 50
SELECT CASE
WHEN CEILING((@aCount-@aFrom+0.0001)/@aRatio) < 0 THEN 0
ELSE CEILING((@aCount-@aFrom+0.0001)/@aRatio) END AS aResult
Is it possible to get the same result without repeating the the "CEILING" part ?
April 17, 2009 at 2:54 am
Bit of a cheat...
SELECT CASE WHEN a < 0 THEN 0 ELSE a END AS aResult
FROM ( SELECT CEILING((@aCount-@aFrom+0.0001)/@aRatio) a ) #t
April 17, 2009 at 2:59 am
Hi,
Try this codes
declare @aFrom int set @aFrom = 30
declare @aRatio int set @aRatio = 20
declare @aCount int set @aCount = 50 -- 0 , 30, 31, 49, 50
declare @RESULT int
select @RESULT = CEILING((@aCount-@aFrom+0.0001)/@aRatio)
SELECT CASE
WHEN @RESULT < 0 THEN 0
ELSE @RESULT END AS aResult
ARUN SAS
April 17, 2009 at 3:17 am
SELECT CASE WHEN @aFrom > @aCount THEN 0 ELSE CEILING((@aCount-@aFrom+0.0001)/@aRatio) END
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 17, 2009 at 1:28 pm
@arun-2 Sathianathan : Nice cheat. But I am sorry to tell you I can't use this because my example is a simplification of the real sql where it is not possible to use a #t 🙁
@arun.sas : Also a nice solution. But the same problem. I can't use this because my example is a simplification of the real sql. 🙁
@chris-2 Morris : I think you gave me direction in where I can find a solution. Think out of the box Henk :hehe:
And sorry for the snippet, being not well formed and missing test-data.
@All : I was hoping for a solution without a CASE WHEN THEN ELSE.
Someting using ISNULL, NULLIF, COALESCE and/or SIGN
April 17, 2009 at 3:54 pm
Ask and ye shall receive...
No promises about efficiency, but it's not a case statement either 😉
-- for any given function or expresion "@X"
declare @x int
set @x = -5
while @x < 5
begin
select @x, ( @X + ABS(@X) ) / 2 -- Magic!!
set @x = @x+1
end
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 17, 2009 at 9:22 pm
This shows how to avoid repeating the expression code withing the case statement by using a derived table in a subquery. This will work for any arbitrary complex expression.
select
a.*,
MyResult =
(select case when x.MyExpression < 0 then 0 else x.MyExpression end
from ( select MyExpression = CEILING((aCount-aFrom+0.0001)/aRatio) ) x )
from
( -- Test Data
select aFrom = 30, aRatio = 20, aCount = 0 union all
select aFrom = 30, aRatio = 20, aCount = 29 union all
select aFrom = 30, aRatio = 20, aCount = 30 union all
select aFrom = 30, aRatio = 20, aCount = 31 union all
select aFrom = 30, aRatio = 20, aCount = 49 union all
select aFrom = 30, aRatio = 20, aCount = 50
) a
Results:
aFrom aRatio aCount MyResult
----------- ----------- ----------- ---------------------------------------
30 20 0 0
30 20 29 0
30 20 30 1
30 20 31 1
30 20 49 1
30 20 50 2
April 19, 2009 at 2:15 pm
Just out of curiousity...
Why are you looking for a solution like
@X = ( @X + ABS(@X) ) / 2
instead of
@X = CASE WHEN @X <0 THEN 0 ELSE @X END
The CASE state is going to outperform the absolute value trick every time.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 3:19 am
That magic approach is to much magic for me, sorry 🙂
About the performance, you have a point. I want to use this in a very heavy used View. So I better stuck with the CASE. Thanks.
An interesting solution, I'll give it a try, to use it in my View.
As I said before, this code snippet is a simplification of the reality, in which I have to use the "negative to zero" 8 times in a complex SUM from a two nested GROUP BY expressions
April 20, 2009 at 6:45 am
Just a suggestion, for readability purposes, use CTEs to build up what you want step-by-step. You don't take a performance hit because the optimizer treats it as a single query.
;With cte1 as
( select {insanely long function goes here} as col_A,
{insanely long function goes here} as col_B,
{insanely long function goes here} as col_C
)
,cte2 as
( select case when col_A < 0 then 0 else col_A end as col_A,
case when col_B < 0 then 0 else col_B end as col_B,
case when col_C < 0 then 0 else col_C end as col_C
)
select col_A, col_B, col_C from cte2
Alternatively, write your own function that simply returns the value passed to it, or returns zero if the value passed to it is negative. If you are going to be using it with large volumes, make sure it is an inline table valued function.
CREATE FUNCTION dbo.ZeroWhenNegative
(
@value int
)
RETURNS TABLE
AS
RETURN
(
select case when @value < 0 then 0 else @value end as value
)
GO
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 7:10 am
Bob, you are very generous to help me so often, thanks.
I don't own the database, so I have to ask if I could add the UDF.
It is an efficient and elegant solution, your NegativeToZero UDF. 😎
The cte/with solution won't work. Because I use (the old) ADO to query the database and the WITH does not pass the ADO MDAC. ADO.NET doesn't have problems with the WITH keyword, but the old Win32 ADO does :angry: .
April 20, 2009 at 7:17 am
Using old school technique, you could use subqueries instead of CTEs to do it in steps. I'm sorry that WITH gives you a problem, because I find CTEs to be much easier to read.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 22, 2009 at 4:04 am
Bob Hovious (4/19/2009)
The CASE state is going to outperform the absolute value trick every time.
Bob
Are you sure about this? I've done some testing:
SET STATISTICS TIME ON
SET NOCOUNT ON
SET STATISTICS IO OFF
GO
DECLARE @i int
SET @i = 1
WHILE @i <= 100
BEGIN
SELECT
number - 1024 -- subtract 1024 so that half the values are negative
,CASE
WHEN number - 1024 < 0 THEN 0
ELSE number - 1024
END AS Result
FROM
master.dbo.spt_values
WHERE
type = 'P'
SET @i = @i + 1
END
DECLARE @i int
SET @i = 1
WHILE @i <= 100
BEGIN
SELECT
number - 1024
,(number - 1024 + ABS(number - 1024))/2 AS Result
FROM
master.dbo.spt_values
WHERE
type = 'P'
SET @i = @i + 1
END
This code uses first the CASE statement and then the ABS function on 2048 values. This is run 100 times in each case. Adding up the execution times for the actual queries, I got this:
CASE statement
[font="Courier New"] CPU time = 392 ms, elapsed time = 6422 ms.[/font]
ABS function
[font="Courier New"] CPU time = 376 ms, elapsed time = 5975 ms.[/font]
This test wasn't 100% scientific because I don't know what other things were going on on the server (my PC) while the queries were running, but it seems to suggest that both queries perform about the same.
John
April 22, 2009 at 7:19 am
John, I'm NOT absolutely sure, having seen your numbers. I would have expected CASE to be less code, basically a single "if". But the arithmetic operations may run faster than I expected. Let me conduct a few of my own time trials. and I'll post back here in a few.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 22, 2009 at 8:41 am
Talk about a photo-finish. 😛
I set up a test against one million rows, run 100 times with half the numbers being negative. (The code for the test is at the bottom.)
Obviously, other processes could push the timings up and down, but on the average the two approaches were remarkably close... averaging 1.67 milliseconds difference for a testing a million rows. (I got lazy and just popped the results into Excel for the averages.)
The times shown are elapsed times, but casual observations with SET STATISTICS TIME ON suggest that the CPU times pretty much stay in proportion to the elapsed times.
If someone wants, they could revise my test to explore other areas such as order by on the result, varying the percentage of negative numbers, different datatypes, etc. I could do that later, but I've got some other stuff going on at the moment.
Thanks, John 🙂
[font="Courier New"]
X AbsvCaseDelta
11186110680
21173109380
31156111046
41153111043
512501610-360
617501546204
721101593517
818131690123
91530148347
1017661420346
111626157650
121673157697
131483147013
1416231703-80
151596157620
1619361626310
1715131736-223
181560153030
19156315630
2018261096730
211156114016
221156112333
231160110654
241156109363
251156109363
261173109380
27356013762184
2822201576644
2914061606-200
3015331563-30
3119831093890
3216861390296
331563150063
3415161703-187
3515931966-373
361766168680
3716861533153
381670158090
3914201923-503
401530148347
4115461720-174
4212501110140
431186110680
4411734640-3467
4515931673-80
4618732280-407
4715931346247
4817161390326
4917202406-686
5015161576-60
5117501576174
5216231986-363
5315131656-143
541546151630
551780175030
56159315930
5715331763-230
5815001703-203
5915632186-623
601733164093
6115631720-157
621170111060
631173110667
64462614663160
6515761923-347
6618601733127
6716562516-860
6817361763-27
6918261376450
7016861920-234
7120461360686
7215801403177
7316261890-264
7414531653-200
7523761860516
7618902030-140
7715801703-123
7816402140-500
7914361530-94
8017662063-297
81209310931000
821156111046
8312034813-3610
8418431640203
8517331436297
8628762483393
872140209347
8814702233-763
8913901610-220
9021231500623
9116561766-110
9216531486167
9319361766170
9421401610530
9516561466190
9617832060-277
9717201873-153
9812831546-263
9913601513-153
10016561826-170
Average1658.11656.431.67 -- in milliseconds[/font]
set nocount on;
set statistics time off;
IF (OBJECT_ID('tempdb..#test') IS NOT NULL) drop table #test
;with tally (N) as (select row_number() over(order by sc.ID) from master..syscolumns sc cross join master..syscolumns sc1)
select top 1000000 N-50001 as testInt
into #test
from tally
select count(*), sum(case when testint < 0 then 1 else 0 end) as negatives
from #test;
declare @X int
declare @timer datetime
declare @elapsedAbsV int
declare @elapsedCase int
declare @timings table(repetition int, Absv int, [Case] int, Delta int)
set @x = 1
while @x <= 100
begin
IF (OBJECT_ID('tempdb..#result1') IS NOT NULL) drop table #result1
IF (OBJECT_ID('tempdb..#result2') IS NOT NULL) drop table #result2
--print '--------- Absolute value'
--set statistics time on;
set @timer = getdate()
select (testInt+abs(testint))/2 as result
into #result1
from #test
set @elapsedAbsV = datediff(ms,@timer,getdate())
--set statistics time off;
set @timer = getdate()
--print '--------- CASE'
--set statistics time on;
select case when testInt < 0 then 0 else testint end as result
into #result2
from #test
set @elapsedCase = datediff(ms,@timer,getdate())
insert into @timings
select @x, @elapsedAbsV, @elapsedCase, @elapsedAbsV - @elapsedCase
--set statistics time off
set @X = @x+1
end
select * from @timings
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply