Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2009-09-09 : 14:35:37
|
Have a table called BanktransactionsRecords look like this:Type TransType Status Amt InvNo ValDt MatchID InvDt W C C -192.6 243 NULL 0 7/14/2009 W C J -279.0 243 9/1/2009 546 7/14/2009 W K J 279.0 240 9/1/2009 546 8/1/2009 W C J -209.25 243 9/1/2009 546 7/14/2009 W K J 209.25 243 9/1/2009 546 9/1/2009 W K J 100.00 555 8/01/2009 555 8/2/2009 W C J -100.00 555 8/1/2009 555 8/2/2009 Creating an Aging Report. Normally if a record does not have a MatchID the Invoice or Payment is open and that is what you want to see. But we have situations where a payment is applied to an order with an invoice date past our cutoff date. Cutoff date = 8/31/2009. I'm trying to get a select statement that would grab the first 4 records above. What I'm thinking is where InvDt<Cuttoff date and ValDt>Cutoff date. But I just can't get it straight in my head. |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-09-09 : 15:49:05
|
| Looks like I've figured it out. I added a field that if MatchID = 0 then "M" and if ValueDate is Greater than Cutoff date then "Y". {BankTransactions.Type} = "W" and{BankTransactions.DebtorNumber} <> "' '" andnot ({BankTransactions.InvoiceNumber} in ["'0'", "' '", "0"]) andnot ({BankTransactions.Status} in ["V"]) andnot ({BankTransactions.TransactionType} in ["T"]) and {@ValueDateGrThanAge} in ["Y", "M"] and {BankTransactions.InvoiceDate}<={?Aging Date} |
 |
|
|
|
|
|
Select Statement