Flagging Line Items

[OU18]

Here is my task,

I have line items as follows

Accrual Month Invoice Date Invoice Type Paidback
8/1/03 8/15/03 MPB $0.00
8/1/03 8/15/03 PICC $40.00
8/1/03 8/15/03 FGD $0.00

When I send my batch to AP, if there is one line item for an invoice date that has a paidback amount greater then 0, I need it to drop out all of the associated line items with it. Therefore the line items with $0.00 would not be in the batch either.

thanks in advance for the help.

 

[Golom]

I'm not sure how you determine that a line item is "associated" but the general mechanism is in the WHERE clause. Assuming that the [Invoice Date] field establishes the association ...

WHERE [Invoice Date] NOT IN
(Select [Invoice Date] From tbl
WHERE [PaidPack] > 0 )

 

[OU18]

okay I put the code in there as follows.

SELECT tblInvoiceTransations.AccountID, tblInvoiceTransations.AccuralMonth, tblInvoiceTransations.InvoiceDate, tblInvoiceTransations.InvoiceID, tblInvoiceTransations.DisputePaidBack
FROM tblInvoiceTransations
WHERE tblInvoiceTransations.InvoiceDate Not In
(Select TblInvoiceTransations.InvoiceDate from tblInvoiceTransations where tblInvoiceTransations.DisputePaidBack>0);

But it takes forever to run, where am I going wrong

 

[Golom]

Now that I see the rest of the SQL we can make this work a little better.

SELECT T.AccountID, T.AccuralMonth, T.InvoiceDate,
T.InvoiceID, T.DisputePaidBack
FROM tblInvoiceTransations As T
WHERE NOT EXISTS
(SELECT *
FROM tblInvoiceTransations As S
WHERE S.InvoiceDate = T.InvoiceDate
AND S.DisputePaidBack>0);

This just determines if a record EXISTS with "DisputePaidBack>0" on the specified date and does not include the records if it does. Make sure that you have an index defined on InvoiceDate.

 

[OU18]

Thank you for your help, i will try this and see if it works. i appreciate your help tremendously

 

[OU18]

I do have another situation I am trying to resolve. I recv back a payment file from our AP group. The linking field in this file is the VoucherID. The VoucherID can have multiple line items in the Transaction Table. However I only wont to post to one of the line items for the voucher, not all of them. How can I make this happen.

 

[Golom]

Which line item? Or will any line item do?

 

[OU18]

preferably the last line item for that voucher id.

 

[Golom]

I don't know your table or field names but here's a model.

Select T.VoucherID, First(P.fld1), First(P.fld2), ...
From Transaction As T INNER JOIN Payment As P
ON T.VoucherID = P.VoucherID
Group By T.VoucherID
Order By T.VoucherID, P.fld1 DESC

I am assuming that "last record" is defined by some ordering of the Payment table on a field. I have assumed that the field that dictates ordering is called "fld1" and have sorted the result set in descending order by that field for each VoucherID. I then pick the first record in the Payment Table as the one I want. (i.e the one with the highest value for fld1.)