Category: Queries – Accounting

Fees By Year and Week

Posted on | by SOS Blog | Leave a Comment on Fees By Year and Week

We want to see a breakdown of fees charged by year and week.

Here we use some interesting functions, such as DATEPART, which returns a specified portion of a date value, as well as more commonly used aggregate functions like SUM, MIN and MAX. We also use the CAST function to return the result of the AVG function as a number with two decimal places. We also add a little spice by throwing in the ROLLUP function to give us totals and subtotals. Where you see NULL in the results, interpret it as meaning “ALL”. Therefore a NULL in the YEAR column should be interpretted as the total line for all the years.

SELECT
  DATEPART(yy,trandate) AS "YEAR",
  DATEPART(wk,trandate) AS "WEEK_Number",
  MIN(trandate) AS "FROM",
  MAX(trandate) AS "THROUGH",
  CAST (AVG(amount) AS DECIMAL(12,2)) AS "AVERAGE FEE",
  SUM(amount) AS "TOTAL FEES CHARGED"
FROM
  sos.journal
WHERE
  trandate BETWEEN '2000-01-01' AND '2003-12-31'
  AND licnum = 101
  AND trantype = 'S'
GROUP BY
  ROLLUP("YEAR","WEEK_Number" )
ORDER BY
  "YEAR","WEEK_Number"

Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year

Posted on | by SOS Blog | Leave a Comment on Fees and Service Count by Payor, CPT, Service, SortCode, Month, and Year

I have a query to tell me the sum of fees and number of services by payor, cpt code, service code, and SortCode, but I would like to break down that data by month and year as well. Can that be done?

This query illustrates the use of a subquery as a virtual table. That is, we use an embedded SELECT statement in the main query’s FROM clause to create a result set that we then treat as if it were a table, JOINing it to other tables to give us the results that we want. To get the month and year grouping,
add YEAR(trandate), MONTH(trandate) to the SELECT list, the GROUP BY, and the ORDER BY.

Note that the column order in the SELECT clause makes no difference, so you can shuffle them around as you like, but be sure to separate columns with commas, except no comma after the last one.

Assuming you would still want the sortcode and payor to be higher order grouping, the query would become:

SELECT
  YEAR(a.trandate) AS "Year",
  MONTH(a.trandate) AS "Month",
  COALESCE(d.payorname,'ALL PAYORS') AS payorname,
  COALESCE(b.cptcode,'ALL CPTS') AS cptcode,
  COALESCE(a.srvcode,'ALL SRVCODES') AS srvcode,
  a.sortcode AS sortcode,
  COUNT(*) AS "N",
  SUM(a.amount) AS "SumFees"
FROM
  (SELECT DISTINCT jnum,srvcode,servicenum,ptpayornum,trandate,amount,sortcode FROM sos.rv_charges) a
  JOIN sos.services b ON a.servicenum = b.servicenum
  JOIN sos.ptpayors c ON a.ptpayornum = c.ptpayornum
  JOIN sos.payors d ON c.payornum = d.payornum
WHERE
  d.payortype = 'I'
  AND trandate BETWEEN '2008-4-01' AND '2008-9-11'
GROUP BY
  ROLLUP(a.sortcode,d.payorname,"Year","Month", b.cptcode, a.srvcode)
ORDER BY
  sortcode,payorname,"Year","Month",cptcode,srvcode

Detailed List of Charge Balances by Payor On Specified Date

Posted on | by SOS Blog | Leave a Comment on Detailed List of Charge Balances by Payor On Specified Date

We have an audit coming up and would like to have a detailed listing, down to the charge split level, of unpaid charges on the last day of our fiscal year. It would be most helpful to have the list sorted by payor and date.

In the following query, you must replace the date in two places with the last date of your fiscal period. Remember that this is a listing of charge splits, so there can be several lines for the same service item, even for a single payor. The sort order keeps splits for the same payor and service together.

SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  a.chgsplamt AS "AmtChargedPayor",
  (SELECT COALESCE(SUM(crsplamt),0)
       FROM sos.jcrsplits
       WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30') AS "PayorCreditsApplied",
  (a.chgsplamt - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  COALESCE(STRING(a.firstbilled),'') AS "DateFirstBilled",
  COALESCE(STRING(a.lastbilled),'') AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
   "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
  AND  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
ORDER BY
  "Payor","SrvDate","TransactionNum"
The following version combines splits if a single payor has more than one split on the same service charge entry.
SELECT
  //patient
  a.licnum AS "DataSet",
  a.id AS "AccountID",
  a.lastname AS "LastName",
  a.firstname AS "FirstName",
  //service
  a.jnum AS "TransactionNum",
  a.trandate AS "SrvDate",
  a.provcode AS "ProvCode",
  a.srvcode AS "SrvCode",
  a.amount AS "TotalFee",
  a.balance AS "TotalBalance",
  //payor
  TRIM(c.payorname + ' ' +c.firstname) AS "Payor",
  SUM(a.chgsplamt) AS "AmtChargedPayor",
  SUM((SELECT COALESCE(SUM(crsplamt),0)
          FROM sos.jcrsplits
          WHERE chgsplnum = a.chgsplnum AND dateapplied <= '2008-09-30')) AS "PayorCreditsApplied",
  ("AmtChargedPayor" - "PayorCreditsApplied") AS "PayorBalance",
  //billing dates
  MIN(COALESCE(STRING(a.firstbilled),'')) AS "DateFirstBilled",
  MAX(COALESCE(STRING(a.lastbilled),'')) AS "DateMostRecentlyBilled"
FROM
  sos.rv_charges a
  JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
  JOIN sos.payors c ON b.payornum = c.payornum
WHERE
  (a.paiddate IS NULL OR a.paiddate > '2008-09-30')
GROUP BY
  "DataSet","Payor","Lastname","Firstname","ID","SrvDate","ProvCode",
  "SrvCode","TotalFee","TotalBalance","TransactionNum"
HAVING
  "AmtChargedPayor" <> 0
  AND "PayorBalance" <> 0
ORDER BY
  "DataSet","Payor","SrvDate","TransactionNum"

	


	





	

			

	

		
Credits Without Sort Codes By Primary Provider
		

			Posted on  |  by SOS Blog |  Leave a Comment on Credits Without Sort Codes By Primary Provider		

			

	

		
We have discovered that some credit entries were entered without specifying a Sort Code, which we need for reporting purposes. I need a total amount of such credits, by provider.


SELECT
 r.provcode,
 r.provlname,
 SUM(amount) AS "TotalCredits"
FROM
 sos.journal j,
 sos.patients p,
 sos.providers r
WHERE
 j.ptnum=p.ptnum AND
 p.providernum=r.providernum AND
 j.trandate BETWEEN '2000-03-01' AND '2016-03-15' AND
 j.sortcode IS NULL
GROUP BY
 r.provcode, r.provlname

	


	





	

			

	

		
In Collection Balances by Provider, Age Group, and Diagnosis
		

			Posted on  |  by SOS Blog |  Leave a Comment on In Collection Balances by Provider, Age Group, and Diagnosis		

			

	

		
Would it be possible to create a query to show total due from collections accounts by primary provider, primary diagnostic code, and age groups of the patient…. 1-5; 6-12; 13-18; 19-28; 29-40; 41 to 60; above 60?


This query demonstrates several interesting features. The first is the use of a CASE expression to evaluate the appropriate age group for each patient, using a custom function, AGEINYEARS( ) that we include with the SOS database. Next is the use of COALESCE( ) to return a desired string (‘None’) when no primary provider or diagnosis has been entered. Finally, we use the GROUP BY ROLLUP(     ) structure to provide us with all possible totals. When examining the result set, you will see NULL under one or more columns on some rows. When reading the results, you should interpret NULL as meaning “ALL”. Therefore, the row that shows:


Primary Provider  = NULL

AgeGroup   = NULL

PrimaryDx = NULL

Balance Due = 100,000


should be read as the grand total of all balances (all age groups, all providers, and all Dx codes)


 


SELECT
  COALESCE(d.provcode,'None') AS "PrimaryProvider",
  (CASE
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 0 AND 5 THEN 'Age under 5'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 6 AND 12 THEN 'Age 6 - 12'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 13 AND 18 THEN 'Age 13 - 18'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 19 AND 28 THEN 'Age 19 - 28'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 29 AND 40 THEN 'Age 29 - 40'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) BETWEEN 41 AND 60 THEN 'Age 41 - 60'
     WHEN sos.AGEINYEARS(c.dob,TODAY() ) > 60 THEN 'Age above 60'
     ELSE 'Age unknown'
  END CASE) AS "AgeGroup",
  COALESCE(e.dxcode1,'None') AS "PrimaryDx",
  SUM(b.balance) AS "Balance Due"
FROM
  sos.payors a
  JOIN sos.ptpayors b ON a.payornum = b.payornum
  JOIN sos.patients c ON b.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcsudx e ON c.ptcsunum = e.ptcsunum
  LEFT OUTER JOIN sos.providers d ON c.providernum = d.providernum
WHERE
  b.balance > 0
  AND a.collectdate IS NOT NULL
GROUP BY ROLLUP( "PrimaryProvider", "AgeGroup", "PrimaryDx")
ORDER BY "PrimaryProvider", "AgeGroup", "PrimaryDx"