Calculate a new field in SQL statement

Question

Calculate a new field in SQL statement

sks_989

SSC Eights!

Points: 843
More actions
July 30, 2022 at 5:42 pm

#4070412

I have need calculate new field on this logic
Is there way to do in SQL instead of procedure or function
We have invoice id and item line item and we have credit memo which apply to line item
Line Item 1 = 2098.76
Line Item 2 = 699.59
Apply CM Amount -2798.35 to both lines
If CM amount >= LineItem1 then CreditAmount = LineItemAmount
Then you need to carry the remainder to check if it cover the other line items:
Apply credit to Line Item 1 = 2098.76 + -2798.35 = -699.59
Apply credit to Line Item 2 = 699.59 + -699.59 = 0
So Line Item 2 CreditAmount = Line Item 2 Amount
At the end, the result should look like this:
invoiceid itemid itemamount CreditAmount
12 1 2098.76 2098.76
12 2 699.59 699.59
Create table invoice ( invoiceid int , itemid int, itemamount money )
Create table creditmemo ( invoiceid int, creditmemoamount money)
insert into invoice Values ( 12 ,1 , 2098.76 )
insert into invoice Values ( 12 ,2 , 699.59 )
insert into creditmemo values( 12 , -1399.00)
insert into creditmemo values( 12 , -1399.35)
select a.invoiceid , itemid , a.itemamount , b.Creditmemoamount
from invoice a
inner join ( select invoiceid, sum(creditmemoamount) Creditmemoamount
from creditmemo group by invoiceid ) b on a.invoiceid = b.invoiceid

August 1, 2022 at 7:33 am

This was removed by the editor as SPAM
August 6, 2022 at 4:25 pm

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

It's only 2 rows of data. To test with different cases I added some additional rows. Afaik you're looking to conditionally apply the sum of invoiceid credits from 'creditmemo' to ordered invoice line items in 'invoices'.

Imo and afaik it's inappropriate to store dollar denominated credits as the 'money' datatype the way it's being used here. There are many articles and documentation available. The purpose of the money datatype is to make intermediate financial calculations "scrutable" (able to be audited to the lowest level) such that each calculation is rounded appropriately and non-significant remainders are truncated. In this case the credits are "the thing itself" which imo could be DECIMAL(14,2)

drop table if exists #invoice;
go
Create table #invoice ( invoiceid int , itemid int, itemamount decimal(14,2) );

drop table if exists #creditmemo;
go
Create table #creditmemo ( invoiceid int, creditmemoamount decimal(14,2));

insert into #invoice Values 
( 11 ,1 , 2098.76 ),
( 11 ,2 , 699.59 ),
( 12 ,1 , 2098.76 ),
( 12 ,2 , 699.59 ),
( 13 ,5 , 2098.76 ),
( 13 ,6 , 699.59 ),
( 13 ,7 , 23699.59 ),
( 13 ,8 , 699.59 ),
( 15 ,7 , 23699.59 ),
( 15 ,8 , 699.59 );

insert into #creditmemo values
( 11 , -1399.00),
( 11 , -1399.35),
( 12 , -1099.00),
( 12 , -399.35),
( 13 , -10399.00),
( 13 , -13099.35);

with cred_cte as (
    select *, iif(row_number() over (partition by invoiceid 
                                         order by itemid)=1, oa.cred, 0) cred_to_apply
    from #invoice i
         outer apply (select sum(c.creditmemoamount)
                      from #creditmemo c
                      where c.invoiceid=i.invoiceid) oa(cred))
select *, 
       iif(sum(isnull(cred_to_apply, 0)+itemamount) over (partition by invoiceid 
                                                              order by itemid)<=0, itemamount, 
           sum(isnull(cred_to_apply, 0)+itemamount) over (partition by invoiceid
                                                              order by itemid)) NewColumn
from cred_cte;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

sks_989 SSC Eights! Points: 843 More actions · Answer 2

Thanks for output but change in the requirement so this will not work.

This is new requirement i got it.

Calcualtionlogic

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

I would ask a couple of questions to the business.

Item ID is unique. Splitting an amount across items based on random lines might not reflect reality. Each line might be better served having matching lines on the credit memo with a reason code. Full or partial credit for each line, and a corresponding reason code would allow for much greater insight.

Damaged in shipping vs. mfg defect for example. Or customer good will for a delay.

If the business does not see this as valuable, I would question why not just add a credit memo line to the invoice and be done. Any analysis to problem solve likely would be meaningless.

sks_989 SSC Eights! Points: 843 More actions · Answer 4

There is no way i can do in SQL so i wrote SP now it is working.

Thanks for the input.