Max Value even for negative numbers

  • Hi,

    Can anyone help me with this. I need to find the max value among 4 numbers in SQL. I tried using the Excel function and its working fine.

    =IF(MIN(A1:A4)=0,MAX(A1:A4)*1.2,MIN(A1:A4)*1.2).

    Having trouble converting that to SQL. With my code its working fine only with positive values. The 4 values could be negative too. Here is SQL working fine for negative values. Any help. Urgent please. Here is the SQL code I have...

    ,isnull(cast((select MAX(V) from (values (COMM1* QTY*1.8), (COMM2* QTY*1.8), (COMM3*QTY*1.8),(COMM4*QTY*1.8)) as value(v)) as numeric(6,2)),0) as RATE

    Advance for the timely help.

    Thanks

    Jagan

  • Is it possible to give table DDL, sample data and desired output?

    Please check the first link in my signature about how to post questions.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Sorry, for not posting the sample data. Here is some sample data attached. I have issues only when the comm2 has negatives values and comm1 & comm3 are 'Zeros'. I have highlighted the correct ones in 'Green' and the incorrect ones in 'red'. I would like some help to fix the one in red. Here is the sql code I used..

    ,isnull(cast((select MAX(V) from (values (COMM1* QTY*1.2), (COMM2* QTY*1.2), (COMM3* CHG_QTY*1.2)) as value(v)) as numeric(6,2)),0) as RATE

    Bunch of thanks

  • deepujagan (5/13/2014)


    Sorry, for not posting the sample data. Here is some sample data attached. I have issues only when the comm2 has negatives values and comm1 & comm3 are 'Zeros'. I have highlighted the correct ones in 'Green' and the incorrect ones in 'red'. I would like some help to fix the one in red. Here is the sql code I used..

    ,isnull(cast((select MAX(V) from (values (COMM1* QTY*1.2), (COMM2* QTY*1.2), (COMM3* CHG_QTY*1.2)) as value(v)) as numeric(6,2)),0) as RATE

    Bunch of thanks

    0 (zero) is greater than a negative

    select MAX(myval) from (values (0),(-1)) as x(myval)

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • That is not true in all the cases.

    Online 14 of the excel sheet I have 2 negative values and a 'zero'. It compared and calculated the highest among the 2 negative values. It suppose to give me result as 'zero'. This is the result set I am exactly looking for in other cases.

    -12.120-4.071-4.88(Correct Rate)

    What I noticed is, if the 2nd column has negative value it is ignored. Please help.

  • first off...the correct way of presenting data...based on your spreadsheet.

    USE [tempdb]

    CREATE TABLE [dbo].[yourtable](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [COMM1] [decimal](18, 2) NULL,

    [COMM2] [decimal](18, 2) NULL,

    [COMM3] [decimal](18, 2) NULL,

    [QTY] [decimal](18, 2) NULL

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT [dbo].[yourtable] ON

    INSERT [dbo].[yourtable] ([ID], [COMM1], [COMM2], [COMM3], [QTY])

    SELECT '1','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '2','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '3','57.35','0.00','0.00','1.00' UNION ALL

    SELECT '4','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '5','101.84','0.00','0.00','1.00' UNION ALL

    SELECT '6','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '7','-36.53','0.00','0.00','1.00' UNION ALL

    SELECT '8','0.00','-0.63','0.00','1.00' UNION ALL

    SELECT '9','12.12','0.00','0.00','1.00' UNION ALL

    SELECT '10','-57.35','0.00','0.00','1.00' UNION ALL

    SELECT '11','0.00','-3.20','0.00','1.00' UNION ALL

    SELECT '12','0.00','0.63','0.00','1.00' UNION ALL

    SELECT '13','-12.12','0.00','-4.07','1.00' UNION ALL

    SELECT '14','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '15','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '16','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '17','57.35','0.00','0.00','1.00' UNION ALL

    SELECT '18','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '19','-57.35','0.00','0.00','1.00' UNION ALL

    SELECT '20','-57.35','0.00','0.00','1.00' UNION ALL

    SELECT '21','12.12','0.00','4.07','1.00' UNION ALL

    SELECT '22','0.00','3.20','0.00','1.00' UNION ALL

    SELECT '23','-36.53','0.00','0.00','1.00' UNION ALL

    SELECT '24','0.00','0.29','0.00','1.00' UNION ALL

    SELECT '25','12.12','0.00','4.07','1.00' UNION ALL

    SELECT '26','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '27','0.00','-0.29','0.00','1.00' UNION ALL

    SELECT '28','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '29','-57.35','0.00','0.00','1.00' UNION ALL

    SELECT '30','0.00','0.00','-5.36','1.00' UNION ALL

    SELECT '31','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '32','-427.27','0.00','0.00','1.00' UNION ALL

    SELECT '33','0.00','0.00','0.00','1.00' UNION ALL

    SELECT '34','0.00','0.00','-5.00','1.00' UNION ALL

    SELECT '35','0.00','-1.60','0.00','1.00' UNION ALL

    SELECT '36','0.00','0.00','-8.23','1.00'

    SET IDENTITY_INSERT [dbo].[yourtable] OFF

    now to start us off again.......

    SELECT id , COMM1, COMM2, COMM3 ,

    ( SELECT CAST( MAX ( V ) as numeric(6,2))

    FROM ( VALUES ( COMM1 * QTY * 1.2 ) ,

    ( COMM2 * QTY * 1.2 ) ,

    ( COMM3 * QTY * 1.2 )

    ) x ( V ))

    FROM yourtable;

    edit> possible solution posted in thread below

    can you please confirm and post back an insert data script with your expected results included please.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • is this anywhere close.....

    SELECT id , COMM1, COMM2, COMM3 , QTY,

    ( SELECT CAST( ISNULL(MAX ( V ) ,0) as numeric(6,2))

    FROM ( VALUES ( NULLIF(COMM1,0) * QTY * 1.2 ),

    ( NULLIF(COMM2,0) * QTY * 1.2 ) ,

    ( NULLIF(COMM3,0) * QTY * 1.2 )

    ) x ( V )) AS RATE

    FROM yourtable

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • yes..I need the largest value among the 3 values and display either a Credit or Debit (+/-).

  • deepujagan (5/13/2014)


    yes..I need the largest value among the 3 values and display either a Credit or Debit (+/-).

    can you please post back an insert data script with your expected results included please...based on the earlier script I wrote for you.

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Since it's only three values, maybe this:

    ...

    ,isnull(cast(CASE ca1.COMM_max WHEN 1 THEN COMM1 WHEN 2 THEN COMM2 ELSE COMM3 END*QTY*1.8 as numeric(6,2)),0) as RATE

    FROM dbo.yourtablename

    CROSS APPLY (

    SELECT CASE

    WHEN ABS(COMM1) > ABS(COMM2) AND ABS(COMM1) > ABS(COMM3) THEN 1

    WHEN ABS(COMM2) > ABS(COMM1) AND ABS(COMM2) > ABS(COMM3) THEN 2

    ELSE 3 END AS COMM_max

    ) AS ca1

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thank you for the inputs. I did get the results in a different way. I pulled the rates first and then added the credit and debit account accordingly and it works fine. Since I need the max value out of the 3 values ABS function worked fine and went a step back to figure out the cr/db account.

    Its little round about...but the problem solved.

    Thanks for all your timely help.

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

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