Post reply

how to ignore between operator end value if it is 0 in where clause

  • April 9, 2015 at 5:28 pm

    #302927

    Hello All Good Evening,

    create table deltavalues (planid varchar(10), baseval float, highval float)

    Insert into deltavalues values ('1002', 2000,5000)

    Insert into deltavalues values ('1003', 1000,0)

    Insert into deltavalues values ('1004', 500,0)

    Insert into deltavalues values ('1005', 1000,2000)

    --I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column

    select * from employee e join deltavalues d on e.planid = d.planid

    where e.balance between d.baseval and d.highval

    but here what i am requesting you is how can i change the highvalue to not include if it is 0

    for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)

    same way for 1004 between 500 and no end values (infinity)

    please advise me in some smart way of doing rather than case statement, if possible please

    how can i write that ?

    please advise

    Thank you in advance

    Milan

  • April 9, 2015 at 8:32 pm

    #1790068

    Any open ideas please?

    Thank you in advance

  • April 9, 2015 at 9:11 pm

    #1790069

    SELECT *

    FROM employee e

    JOIN deltavalues d

    ON e.planid = d.planid

    WHERE e.balance BETWEEN d.baseval AND

    CASE d.highval

    WHEN 0 THEN 999999 -- or whatever higher number you need

    ELSE d.highval

    END

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply