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