May 18, 2009 at 12:47 pm
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
May 18, 2009 at 1:40 pm
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