I need a list and total of reversed unapplied credits for a period of time.
Well, once you understand that reversing an unapplied credit generates a REFUND charge entry, all you have to do is to search for creditsplits applied to REFUND charges:
SELECT
*
FROM
sos.rv_creditsplits
WHERE
srvcode = 'REFUND'
Now, there are several dates available among the columns in rv_creditsplits, but if you want to get the dates when the reversal was done, then you would add a filter to just look at the dateapplied date range:
SELECT
*
FROM
sos.rv_creditsplits
WHERE
srvcode = 'REFUND'
AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31'
This query will return all the columns in rv_creditsplits, so you should replace the * in the second line with the column names you want, separated by commas.
The form above also will return both the positive and negative credit splits. You might be refunding only part of the unapplied amount, so you want to concentrate on the negative amounts — the amounts actually reversed, so add: AND crsplamt < 0, like so:
SELECT
*
FROM
sos.rv_creditsplits
WHERE
srvcode = 'REFUND'
AND crsplamt < 0
AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31'
For your summary, you probably just want the total of the reversals, but expressed as a positive amount, so we query the sum of the credit split amounts and multiply by minus 1:
SELECT
SUM(crsplamt) * (-1)
FROM
sos.rv_creditsplits
WHERE
srvcode = 'REFUND'
AND crsplamt < 0
AND dateapplied BETWEEN '2012-01-01' AND '2012-01-31'
