Friday, December 11, 2009

Fee Applications by Credit Document

The following code will return how a specific document (or group of documents) have been applied from a fee perspective.
SELECT
linvoice AS 'Invoice #',
ldocumnt AS 'Payment #',
ltradat AS 'Payment Date',
llcode AS 'Ledger Code',
tkinit AS 'Tk #',
tkblast AS 'Tk Name',
sum(lfamnt) as 'Fee Amt'
from ledgfee
INNER JOIN ledger on ledgfee.lindex = ledger.lindex
INNER JOIN timekeep on lftk = tkinit
where 
linvoice = 11714
group by linvoice, ldocumnt, ltradat, llcode, tkinit, tkblast
Results
Invoice #Payment #Payment DateLedger CodeTk #Tk NameFee Amt
11714 3245 2001-04-06 PAY 1234 Stevens 1400
11714 3245 2001-04-06 PAY 6514 Wright 4400
11714 3245 2001-04-06 PAY 7381 Baker 3250
11714 3245 2001-04-06 PAY 8477 Williams 475

No comments:

Post a Comment