If I am looking back at total credits by cash over a 10 year period and I wanted specifics on when the cash was posted and what the client name is that we received and posted cash to there account…how do I go about getting that?
See the appropriate “How-To” above for instructions about how to output the results to Excel or to a form you can print.
SELECT DISTINCT
lastname +', '+ firstname +' / '+ "id" AS "Client Name",
srv_date AS "Date of Service",
cre_date AS "Date of Payment",
cre_amount AS "Amount Paid",
j.daybatch AS "Daysheet"
FROM
sos.rv_creditsplits cs
JOIN sos.journal j ON cs.srv_jnum = j.jnum
WHERE
cs.licnum = 101
AND credtype = 'cash'
AND cre_date BETWEEN '1999-01-01' AND '2011-12-31'
ORDER BY
"Client Name","Date of Payment","Date of Service","Amount Paid"
For the grand total, use this query:
SELECT
SUM(amount)
FROM
sos.rv_credits c
WHERE
licnum = 101
AND credtype = 'cash'
AND trandate BETWEEN '1999-01-01' AND '2011-12-31'