# T-SQL: How to avoid losing digits when divided by a number

We have a product 'A' which is billed once but delivered to the customer in multiple phases example 'A1', 'A2', 'A3'. I need a report for these phases

Suppose Amount of 'A' is 100, so to fetch amount of 'A1', 'A2', 'A3' I divide the amount by 3.

Actual Output
Description Amount

``````A1          33.33
A2          33.33
A3          33.33
``````

But the total comes to be 99.99 which is creating a problem for the accountants.

Is there a built-In function or a method where I could fetch the output as given below.

Desired Output

``````Description Amount
A1          33.33
A2          33.33
A3          **33.34**
``````

I do not have phase wise data stored(33.33).

I have two tables: Product table, which has Name of the product and Amount i.e. 'A' and '100' and the other is the Frequency table which has the product and frequency i.e 'A' and '3'. I want any one of these 3 phases to have amount as '33.34' such that when I add these 3 amounts the total is '100' and not '99.99'

• Usually a product have a unit price, if you deliver 50, 25, 25 you should consider to store 50 * unit_price, 25 * unit_price and 25 * unit_price. – McNets Aug 8 '17 at 10:13
• What would you charge a person who genuinely ordered 33.33? Are these "measured" (weighed?) amounts or countable things. If the latter, then 33,33,34 seems the right way to go, if the former, then 33.33 three times and don't worry about the 0.01 – dsz Aug 8 '17 at 12:39

Your problem is that you try to solve something mathematically, not by accounting standards.

For the LAST payment, do not use your formula. Instead take the full price and substract all partial prices.

So, A3 would be 100 - A1 - A2 and that would then come to a perfect number.

So, either the first or the last payment are calculated as "the rest missing from the others" instead of "one equal part".

problem appear simple,but "how to implement same thing in particular circumstances" is main question.

any way I have tried to emaluate your problem in my example.

Test with other sample data.

``````declare @product table(productid int,amount int)
insert into @product VALUES(1,100),(2,150)

declare @Frequncy table(productid int,frequncy int)
insert into @Frequncy VALUES(1,3),(2,3)

select p.productid
, p.amount/casT(f.frequncy as decimal(2,1)) RegularInstallment
,case when p.amount%f.frequncy >0 then
p.amount-(p.amount/casT(f.frequncy as decimal(2,1)))*(f.frequncy-1)
else p.amount/casT(f.frequncy as decimal(2,1)) end LastInstallment
from @product p
inner join @Frequncy F
on p.productid=f.productid
``````

I created a Table Valued Function and used CROSS APPLY to solve this - it should get you going.

Create the TVF

``````IF EXISTS (SELECT *
FROM   sys.objects
WHERE  object_id = OBJECT_ID(N'[dbo].[FrequencyAmountForProduct]')
AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[FrequencyAmountForProduct]

GO

Create FUNCTION [dbo].[FrequencyAmountForProduct] (
@Product VARCHAR(5)
,@Amount DECIMAL(18, 2)
,@Frequency SMALLINT
)
RETURNS @temptable TABLE (
FrequencyId VARCHAR(50)
,FrequencyAmount DECIMAL(18, 2)
)

BEGIN

DECLARE @IndividualFrequencyAmount DECIMAL(18, 2) = @Amount / @Frequency
DECLARE @FrequencyCounter SMALLINT = 1

WHILE @Frequency > 1    --Use @IndividualFrequencyAmount for all but last payment
BEGIN
INSERT INTO @temptable (
FrequencyId
,FrequencyAmount
)
SELECT convert(VARCHAR(5), @Product) + convert(VARCHAR(5), @FrequencyCounter)
,@IndividualFrequencyAmount

SET @FrequencyCounter += 1
SET @Frequency -= 1
SET @Amount -= @IndividualFrequencyAmount   --Reduce @Amount by @IndividualFrequencyAmount
END

IF @amount > 0  --If @amount is > 0, insert one more row with remainder
BEGIN
INSERT INTO @temptable (
FrequencyId
,FrequencyAmount
)
SELECT convert(VARCHAR(5), @Product) + convert(VARCHAR(5), @FrequencyCounter)
,@Amount
END

RETURN
END
GO
``````

Demo

``````declare @product table(product varchar(5),amount int)
insert into @product VALUES('A',175),('B',265)

declare @Frequency table(product varchar(5),frequency int)
insert into @Frequency VALUES('A',3),('B',7)

SELECT z.FrequencyId,z.FrequencyAmount from @product pr join
@Frequency fr on fr.product=pr.product
cross apply [dbo].[FrequencyAmountForProduct](pr.Product, pr.Amount, fr.Frequency) z
``````

Results

``````| FrequencyId   | FrequencyAmount   |
|-------------  |-----------------  |
| A1            | 58.33             |
| A2            | 58.33             |
| A3            | 58.34             |
| B1            | 37.86             |
| B2            | 37.86             |
| B3            | 37.86             |
| B4            | 37.86             |
| B5            | 37.86             |
| B6            | 37.86             |
| B7            | 37.84             |
``````
• Given script is not upto the mark because RBAR operation is not require.While thinking about TVF is ok but it also depend upon what the real scenario look like. – KumarHarsh Aug 9 '17 at 3:31