时间戳

首页 > 解决方案 > Issue while adding values in SQL Server

sql-server - 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.

标签： sql-server

解决方案

认识到您没有产生实际的数字结果，我认为尝试使用这种数据类型1保持它是不健康的。

如果我了解您的要求，这将产生您正在寻找的字符串：

;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

（未测试负值。可能需要进行一些调整。技术上%计算模数而不是余数等）

1因为有人可能会尝试将其中两个值相加，但我怀疑这会产生正确的结果，即使使用相同的值Divisor来计算两者也不一定。

推荐阅读