May 12, 2014 at 5:02 pm
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
May 13, 2014 at 12:12 am
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
May 13, 2014 at 4:07 am
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
May 13, 2014 at 4:43 am
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
May 13, 2014 at 5:02 am
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.
May 13, 2014 at 5:31 am
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
May 13, 2014 at 5:59 am
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
May 13, 2014 at 6:07 am
yes..I need the largest value among the 3 values and display either a Credit or Debit (+/-).
May 13, 2014 at 6:34 am
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
May 13, 2014 at 12:54 pm
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".
May 13, 2014 at 6:52 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy