T-SQL Query ???

• cdi_h

  Old Hand

  Points: 380

  More actions

  May 18, 2009 at 12:47 pm

  #75469

  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.

  PLEASE HELP.

  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

• FelixG

  SSCrazy

  Points: 2550

  More actions

  May 18, 2009 at 1:40 pm

  #996599

  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