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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy