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