Finding Minumum Value but skipping 0

  • Hi,

    I need to find minimum value in a two colums but it shouldnt consider 0 while processing.

    i have following resultset

    Date Name Buy Sell

    ----------------------------------------

    1-1-2008 A 1 4

    1-1-2008 A 0 1

    1-1-2008 A 2 0

    1-1-2008 A 0 0

    1-1-2008 B -1 4

    1-1-2008 B 0 1

    1-1-2008 B 3 6

    1-1-2008 B 0 4

    I would expect result like:

    Date Name Buy Sell

    ----------------------------------------

    1-1-2008 A 1 1

    1-1-2008 B -1 1

    Thanks in advance.

  • Something like this works:

    Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)

    INSERT INTO @stocks (

    dAte,

    [nAme],

    Buy,

    sell

    )

    SELECT

    '1-1-2008', 'A', 1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'A', 2, 0

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 0

    UNION ALL

    SELECT

    '1-1-2008', 'B', -1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'B', 3, 6

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 4

    SELECT

    date,

    [name],

    MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End) AS buy,

    MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End) AS sell

    FROM

    @stocks

    GROUP BY

    date,

    [name]

    You can replace the constant value with 100 with a more outrageous number if need be.

    Note how I provided the test data. It makes helping you easier and faster

  • Jack Corbett (6/4/2009)


    Something like this works:

    Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)

    INSERT INTO @stocks (

    dAte,

    [nAme],

    Buy,

    sell

    )

    SELECT

    '1-1-2008', 'A', 1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'A', 2, 0

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 0

    UNION ALL

    SELECT

    '1-1-2008', 'B', -1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'B', 3, 6

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 4

    SELECT

    date,

    [name],

    MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End) AS buy,

    MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End) AS sell

    FROM

    @stocks

    GROUP BY

    date,

    [name]

    You can replace the constant value with 100 with a more outrageous number if need be.

    Note how I provided the test data. It makes helping you easier and faster

    If you don't mind the the following:

    Warning: Null value is eliminated by an aggregate or other SET operation.

    You could replace the 100 with null (no quotes around the word).

  • Hi and thanks for that, i havent tried , before that i have a doubt. Suppose i have a set which has all 0 like for example

    Sell

    ----

    0

    0

    0

    and i use case and replace it with 'x' where x is very large like 99999999 .

    so wont i get this number a my minimum ?

    sorry if i sound foolish but i m a newbie.

  • Or, if you still don't mind the warning above, this would work as well:

    Declare @stocks TABLE([Date] SMALLDATETIME, [Name] CHAR(1), Buy INT, Sell INT)

    INSERT INTO @stocks (

    [Date],

    [Name],

    Buy,

    Sell

    )

    SELECT

    '1-1-2008', 'A', 1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'A', 2, 0

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 0

    UNION ALL

    SELECT

    '1-1-2008', 'B', -1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'B', 3, 6

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 4;

    select * from @stocks;

    SELECT

    [Date],

    [Name],

    MIN(nullif(Buy,0)) AS Buy,

    MIN(nullif(Sell,0)) AS Sell

    FROM

    @stocks

    GROUP BY

    [Date],

    [Name]

  • Lynn's solution will work, is simpler, and doesn't need an arbitrary number. The only issue is that some UI's don't like the warning so you need to SET ANSI_WARNINGS OFF.

    Here's my solution modified to output NULL when you have all 0's:

    Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)

    INSERT INTO @stocks (

    dAte,

    [nAme],

    Buy,

    sell

    )

    SELECT

    '1-1-2008', 'A', 1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'A', 2, 0

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 0

    UNION ALL

    SELECT

    '1-1-2008', 'B', -1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'B', 3, 6

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 4

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 6

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 4

    SELECT

    date,

    [name],

    NULLIF(MIN(CASE WHEN buy = 0 THEN 100 ELSE Buy End), 100) AS buy,

    NULLIF(MIN(CASE WHEN sell = 0 THEN 100 ELSE sell End), 100) AS sell

    FROM

    @stocks

    GROUP BY

    date,

    [name]

    Here's Lynn's solution with the ANSI_WARNINGS added:

    Declare @stocks TABLE([dAte] SMALLDATETIME, [nAme] CHAR(1), Buy INT, sell INT)

    INSERT INTO @stocks (

    dAte,

    [nAme],

    Buy,

    sell

    )

    SELECT

    '1-1-2008', 'A', 1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'A', 2, 0

    UNION ALL

    SELECT

    '1-1-2008', 'A', 0, 0

    UNION ALL

    SELECT

    '1-1-2008', 'B', -1, 4

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'B', 3, 6

    UNION ALL

    SELECT

    '1-1-2008', 'B', 0, 4

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 1

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 6

    UNION ALL

    SELECT

    '1-1-2008', 'C', 0, 4

    SET ANSI_WARNINGS OFF

    SELECT

    date,

    [name],

    MIN(NULLIF(Buy, 0)) AS buy,

    MIN(NULLIF(sell, 0)) AS sell

    FROM

    @stocks

    GROUP BY

    date,

    [name]

    SET ANSI_WARNINGS ON

    You can also set ANSI_WARNINGS OFF in the connection string although I don't recommend it.

  • Thanks a lot Jack Corbett and Lynn Pettis its worked !!!

    i used case with null.

    Thanks again 🙂 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

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