T-SQL Query ???

  • Table Structure - Invoices

    InvoiceNumber InvoiceAmount LineItem

    B1234567 1000.00 1

    A12345 100.00 1

    B1234567 1500.00 2

    B1234567 1200.00 3

    C12345678 100.00 1

    The table structure is given above with example data.

    What I would like to with my query is when I insert a new invoice,

    (a) if it is an existing INvoice Number and the amount is not the same as line item 1, then I would like to take the max of line item + 1 and then insert the new record into the table.

    Example: If I want to to insert a new record into Invoices table, with Invoice# as B1234567 & Amount as 160.00, it would be insert as the amount of this invoice for LineItem #1 (i.e 1000.00) is not the same, so it would be insert with LineItem as 4 (as max of lineitem for this invoice# is 3).

    The query is give below and it is not working.


    All new invoices are in a temporary table - #tbl_Invoices

    Insert into Invoices (InvoiceNumber,Invoice_Dt,InvoiceAmount,LineItem)

    Select TI.Invoice_Num,TI.Invoice_Dt,TI.Invoice_Amount,INV.LineItem + 1 from #tbl_Invoices TI

    ,(Select Distinct InvoiceNumber,InvoiceAmount,max(lineItem) As LineItem from Invoices Group by InvoiceNumber,InvoiceAmount) As INV

    Where TI.Invoice_num =INV.InvoiceNumber

    And TI.Invoice_Amount <> INV.InvoiceAmount

  • insert Invoices

    select distinct B.Invoice_num, B.Invoice_Amount, max(A.line_item)+1 from Invoices A

    inner join #tbl_invoices B

    ON a.Invoice_num= B.Invoice_num and Invoice_AmountB.Invoice_Amount

    group by B.Invoice_num, B.Invoice_Amount

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

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