Convert Excel SQL query to TSQL

[neemi]

Hi I have a bit of sql used within excel and I want to convert this to use within SQL Server.

MAX (
        ROUNDDOWN ( 1 / Inner_Carton_Dim_Width_M , 0) * ROUNDDOWN ( 1.2 / Inner_Carton_Dim_Length_M, 0) ,
        ROUNDDOWN ( 1 / Inner_Carton_Dim_Length_M, 0) * ROUNDDOWN ( 1.2 / Inner_Carton_Dim_Width_M, 0) ,
        1
    )

I know I can use the floor function instead of the Rounddown but How do I get the max of the list.

What I have so far is:

(MAX(
				FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]),
				FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]),
				1)

Help appreciated.

neemi

 

[RiverGuy]

Try this. I'm not sure which one Excel picks if both are equal under that MAX function, but you can adjust this query accordingly if needed:

SELECT
MaxNumber =
CASE
  WHEN FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]) >= FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]) THEN FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M])
  ELSE FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M])
END
FROM YourTable

 

[bborissov]

MAX() is aggregate function in SQL Server. You should use CASE statement:

(CASE WHEN FLOOR(1/[Inner_Carton_Dim_Width_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Length_M]) >
           FLOOR(1/[Inner_Carton_Dim_Length_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Width_M]) AND
           FLOOR(1/[Inner_Carton_Dim_Width_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Length_M]) > 1
      THEN FLOOR(1/[Inner_Carton_Dim_Width_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Length_M])
      WHEN FLOOR(1/[Inner_Carton_Dim_Length_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Width_M]) >
           FLOOR(1/[Inner_Carton_Dim_Width_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Length_M]) AND
           FLOOR(1/[Inner_Carton_Dim_Length_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Width_M]) > 1
      THEN FLOOR(1/[Inner_Carton_Dim_Length_M])*
           FLOOR(1.2/[Inner_Carton_Dim_Width_M]) 
 ELSE 1 END

NOT TESTED!!!!!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[neemi]

Thanks for all your help.

I have the finished extract of the sql and though it may be an idea to create a function to return this.

I need to create Function GetPalletTi and Function GetPalletHi

The sql for GetPalletTi would be:

SELECT
(
	CASE
		WHEN BT.[Direct_Sourcing] ='N' THEN 
			PD.[Domestic_Ti]
		ELSE 
			(CASE
				WHEN (FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]) >=
					 FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M])) 
				AND  FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]) > 1 
				THEN
					 FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M])
				
				WHEN (FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]) >=
					 FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M])) 
				AND  FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]) > 1 
				THEN
					 FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M])
				ELSE 1
			END) 				
		END) AS [TI]
FROM
	(Traders BT 
	INNER JOIN
		Product PD
	ON
		BT.[RP_Ref] = PD.[RP_Ref])
WHERE
	BT.[RP_Ref] = refParameter

help appreciated in converting to function where refParameter is the parameter to pass.

cheers,
nemei

 

[neemi]

Can Someone please check my function for me to see if it is correct please. Very much appreciated.

[/code]
CREATE FUNCTION functi()
(@RP_Ref Int)
RETURNS Int
AS
BEGIN
DECLARE @ReturnTi Int

SELECT @ReturnTi = (
CASE
WHEN BT.[Direct_Sourcing] ='N' THEN
PD.[Domestic_Ti]
ELSE
(CASE
WHEN (FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]) >=
FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]))
AND FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]) > 1
THEN
FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M])

WHEN (FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]) >=
FLOOR(1/[Inner_Carton_Dim_Width_M])*FLOOR(1.2/[Inner_Carton_Dim_Length_M]))
AND FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M]) > 1
THEN
FLOOR(1/[Inner_Carton_Dim_Length_M])*FLOOR(1.2/[Inner_Carton_Dim_Width_M])
ELSE 1
END)
END)
FROM
(Rp_Buyers_Traders BT
INNER JOIN
Product_details PD
ON
BT.[RP_Ref] = PD.[RP_Ref])
WHERE
BT.[RP_Ref] = @RP_Ref

RETURN @ReturnTi

END
[/code]

help appreciated.
neemi

 

[gmmastros]

There is only 1 problem that I see.

[tt][blue]
CREATE FUNCTION functi[!]()[/!]
[!]([/!]@RP_Ref Int[!])[/!]
RETURNS Int
AS
etc...
[/blue][/tt]

You need to have parenthesis around your parameters, but in this case, you have 2 sets of them. Remove the first set.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom