CODEWITHSUNDEEP
sql-server
Hitesh Shroff
Hitesh Shroff

Reputation: 71

Issue while adding values in SQL Server

Please read again till end (description updated)
I want something like this.
ex : if (7200 / 42) is float then
floor(7200/42) + [7200 - {(floor(7200/42)) * 42}] / 10 ^ length of [7200 - {(floor(7200/42)) * 42}]
STEP : 1 => 171 + ((7200 - (171*42))/10 ^ len(7200-7182))
STEP : 2 => 171 + ((7200 - 7182)/10 ^ len(18))
STEP : 3 => 171 + (18/10 ^ 2)
STEP : 4 => 171 + (18/100)
STEP : 5 => 171 + 0.18
STEP : 6 => 171.18

I have written the code in SQL which actually works perfectly but the addition of 171 + 0.18 only gives 171

IF I can get "171/18" instead of "171.18" as string then it'd also be great. (/ is just used as separator and not a divison sign)

Following is the code I written
Here,
(FAP.FQTY + FAP.QTY) = 7200,
PRD.CRT = 42
(values only for example)

select 
case when PRD.CRT <> 0 then
    case when (FAP.FQTY + FAP.QTY)/PRD.CRT <> FLOOR((FAP.FQTY + FAP.QTY)/PRD.CRT) then --DETERMINE WHETHER VALUE IS FLOAT OR NOT
        (floor((FAP.FQTY + FAP.QTY)/PRD.CRT)) +
        ((FAP.FQTY + FAP.QTY) - floor((FAP.FQTY + FAP.QTY)/PRD.CRT) * PRD.CRT) /
        POWER(10, len(floor((FAP.FQTY + FAP.QTY) - floor((FAP.FQTY + FAP.QTY)/PRD.CRT) * PRD.CRT))) 
    else 
        (FAP.FQTY + FAP.QTY)/PRD.CRT -- INTEGER
    end 
else
    0
end
from FAP inner join PRD on FAP.Comp_Year = PRD.Comp_Year and
FAP.Comp_No = PRD.Comp_No and FAP.Prd_Code = PRD.Prd_Code

I got all the values correct till 171 + 0.1800 correct but after that I am only receiving 171 in the addition. I want exactly 171.18.

REASON FOR THIS CONFUSING CALCULATION
Its all about accounting
Suppose, a box(or a cartoon) has 42 nos. of items.
A person sends 7200 items. how many boxes he has to send?
So that will be (7200/42) = 171.4257.
But boxes cannot be cut (its whole number i.e 171).
so 171 * 42 ie 7182 items.
Remaining items = 7200 - 7182 = 18.
So answer is 171 boxes and 18 items.
In short 171.18 or "171/18"

Please help me with this..

Thank you in advance.

Upvotes: 0

Views: 98

Answers (4)

LukStorms
LukStorms

Reputation: 29657

Just another idea about how to calculate it.

Simple calculate the whole boxes.
And concatinate a dot with the remaining items (using a modulus).
Wrapped it all up in a CASE WHEN (or IIF) to avoid the divide by zero.

Example snippet: 

declare @TestTable table (FQTY numeric(18,2), QTY numeric(18,2), CRT numeric(18,0));

insert into @TestTable (FQTY,QTY,CRT) values 
(5000, 2200, 42),
(5000, 2200, 0),
( 100,  200, 10);

select *,
 (CASE
  WHEN CRT>0
  THEN CONCAT(CAST(FLOOR((FQTY+QTY)/CRT) as INT),'/',CAST((FQTY+QTY)%CRT as INT))
  ELSE '0'
  END) AS Boxes
from @TestTable;

Result:

FQTY    QTY     CRT  Boxes
------- ------- ---  ------
5000.00 2200.00  42  171/18
5000.00 2200.00   0       0
 100.00  200.00  10    30/0

The CONCAT returns a varchar, and so does the CASE WHEN.
But you could wrap that CASE WHEN in a CAST.

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

if i understand your logic correctly you want the remainder of 7200 divide by 42 and the remainder is to divide by 100

declare 
    @dividend   int = 7200,
    @divisor    int = 42

select  (@dividend / @divisor) 
+       convert(decimal(10,4), 
                (@dividend % @divisor) * 1.0 / power(10, len(@dividend % @divisor)))

EDIT: change to handle the 10^len(remainder)

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Recognise that you're not producing an actual numeric result, I'd describe it as unhealthy to try to keep it using such a datatype1.

This produces the strings you're seeking, if I've understood your requirement:

;With StartingPoint as (
    select 7200 as Dividend, 42 as Divisor
)
select
    CONVERT(varchar(10),Quotient) +
        CASE WHEN Remainder > 0 THEN '.' + CONVERT(varchar(10),Remainder)
        ELSE '' END as FinalString
from
    StartingPoint
        cross apply
    (select Dividend/Divisor as Quotient, Dividend % Divisor as Remainder) t

(Not tested for negative values. Some adjustments may be required. Technically % computes the modulus rather than the remainder, etc)

1Because someone might try and add two of these values together and I doubt that produces a correct result, not even necessarily if using the same Divisor to compute both.

Upvotes: 1

Terry Carmen
Terry Carmen

Reputation: 3886

You're getting an automatic type conversion from int to decimal(10,0) which is probably not what you want.

https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-2017

Check out the "Caution" box.

If you want a specific amount of precision, you'll need to explicitly cast() the values to the desired data type.

Upvotes: 0

Related Questions