Reversed unapplied credits (refunds) for a period

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'

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.