1

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'

  • 1
    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
1

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".

| improve this answer | |
1

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
| improve this answer | |
1

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             |
| improve this answer | |
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.