jfirestone
Registered User.
- Local time
- Yesterday, 20:52
- Joined
- May 12, 2004
- Messages
- 10
Here is my query as it stands.
SELECT t.AR_OPN_CUS_NO, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_DUE_DT, t.AR_OPN_DOC_TP, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_NO, t.AR_OPN_APPLY_TO, t.AR_OPN_REF, t.AR_OPN_AMT1, t.AR_OPN_AMT2, Sum(t.Sub) AS SumOfSub
FROM AROpen AS t INNER JOIN FindBalances AS q ON (t.AR_OPN_APPLY_TO = q.AR_OPN_APPLY_TO) AND (t.AR_OPN_CUS_NO = q.AR_OPN_CUS_NO)
GROUP BY t.AR_OPN_CUS_NO, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_DUE_DT, t.AR_OPN_DOC_TP, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_NO, t.AR_OPN_APPLY_TO, t.AR_OPN_REF, t.AR_OPN_AMT1, t.AR_OPN_AMT2
HAVING (((t.AR_OPN_DOC_DT)<Date()-15))
ORDER BY t.AR_OPN_CUS_NO, t.AR_OPN_APPLY_TO;
The place it needs fixing is the part where I have it eliminating records that are within 15 days of todays date.
HAVING (((t.AR_OPN_DOC_DT)<Date()-15))
I do want it to eliminate those records, unless they are part of a series that should cancel each other out. I have reduced the amount of info as most of the other fields are irrelevant to this query, just extra info for my report.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/18/04,5/30/04,Inv,123456,123456,100.00
11111,5/25/04,5/25/04,Pmt,003526,123456,100.00
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
11111,6/28/04,6/28/04,Pmt,003669,234567,190.00
11111,6/28/04,6/28/04,Inv,345678,345678,150.00
When I get the result of my query, I currently get this.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
The query should cancel out any records that have the same APPLY_TO, where the Sub=0, this it is doing. However the problem is when I have an invoice and a payment that do NOT match. Above, the query returned the invoice only ($200.00) not the invoice and the payment ($190.00). It does not show the payment because I told it not to show me anything inside of 15 days. I need it to show the payment so that I know the invoice was underpaid, rather than not paid at all.
This is what i should get back.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
11111,6/28/04,6/28/04,Pmt,003669,234567,190.00
Any help?
Thank you!
SELECT t.AR_OPN_CUS_NO, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_DUE_DT, t.AR_OPN_DOC_TP, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_NO, t.AR_OPN_APPLY_TO, t.AR_OPN_REF, t.AR_OPN_AMT1, t.AR_OPN_AMT2, Sum(t.Sub) AS SumOfSub
FROM AROpen AS t INNER JOIN FindBalances AS q ON (t.AR_OPN_APPLY_TO = q.AR_OPN_APPLY_TO) AND (t.AR_OPN_CUS_NO = q.AR_OPN_CUS_NO)
GROUP BY t.AR_OPN_CUS_NO, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_DUE_DT, t.AR_OPN_DOC_TP, t.AR_OPN_DOC_DT, t.AR_OPN_DOC_NO, t.AR_OPN_APPLY_TO, t.AR_OPN_REF, t.AR_OPN_AMT1, t.AR_OPN_AMT2
HAVING (((t.AR_OPN_DOC_DT)<Date()-15))
ORDER BY t.AR_OPN_CUS_NO, t.AR_OPN_APPLY_TO;
The place it needs fixing is the part where I have it eliminating records that are within 15 days of todays date.
HAVING (((t.AR_OPN_DOC_DT)<Date()-15))
I do want it to eliminate those records, unless they are part of a series that should cancel each other out. I have reduced the amount of info as most of the other fields are irrelevant to this query, just extra info for my report.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/18/04,5/30/04,Inv,123456,123456,100.00
11111,5/25/04,5/25/04,Pmt,003526,123456,100.00
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
11111,6/28/04,6/28/04,Pmt,003669,234567,190.00
11111,6/28/04,6/28/04,Inv,345678,345678,150.00
When I get the result of my query, I currently get this.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
The query should cancel out any records that have the same APPLY_TO, where the Sub=0, this it is doing. However the problem is when I have an invoice and a payment that do NOT match. Above, the query returned the invoice only ($200.00) not the invoice and the payment ($190.00). It does not show the payment because I told it not to show me anything inside of 15 days. I need it to show the payment so that I know the invoice was underpaid, rather than not paid at all.
This is what i should get back.
CUS_NO,DOC_DT,DOC_DUE_DT,DOC_TP,DOC_NO,APPLY_TO,Sub
11111,5/30/04,6/15/04,Inv,234567,234567,200.00
11111,6/28/04,6/28/04,Pmt,003669,234567,190.00
Any help?
Thank you!
Last edited:
