Calculate a new field in SQL statement

  • 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

     

  • 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

  • This was removed by the editor as SPAM

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

    This is new requirement i got it.

    Calcualtionlogic

     

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

     

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

    Thanks for the input.

     

  • This was removed by the editor as SPAM

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

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