Collections by Service for Period

“I have a need for a report that would breakdown monies collected for each service code over a specified time period.”

This one selects based on the service date.
SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode
This one also selects based on the service date, but adds a count of the number of services and the average payment per service.
SELECT 
   srvcode, 
   SUM(crsplamt) AS "Total Payments",
   COUNT(DISTINCT srv_jnum) AS "Count",
   CAST(ROUND("Total Payments"/"Count",2) AS DECIMAL(12,2)) AS "Avg Payment per Svc"
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND Srv_Date BETWEEN '2001-01-01' AND '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode
This one selects based on the date payment was applied.
SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
   AND DateApplied BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY 
   srvcode
ORDER BY 
   srvcode
This one selects based on the date payment was received.
SELECT 
   srvcode, 
   sum(crsplamt) AS Payments
FROM 
   sos.rv_creditsplits
WHERE 
   CredType <> 'Adjustment'
AND 
   Cre_Date BETWEEN '2001-01-01' and '2001-12-31'
GROUP BY
   srvcode
ORDER BY 
   srvcode

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.