Author: SOS Blog

Intake Count for Period

Posted on | by SOS Blog | Leave a Comment on Intake Count for Period 
“Is there a way that I might be able to run a report that will tell me the exact number of intakes in a given
period of time?”
SELECT
   Patients.LicNum, 
   Providers.ProvCode, 
   Count(*) as "Count"
FROM
   sos.Patients
   LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum
WHERE
   Patients.IntakeDate BETWEEN '2003-01-01' AND '2003-12-31'
GROUP BY
   Patients.LicNum, 
   Providers.ProvCode
ORDER BY
   Patients.LicNum, 
   Providers.ProvCode
The "Licnum" represents the data set number. If you have only one data set, you can leave that out.

Payment Latency for Specified Payor Number

Posted on | by SOS Blog | Leave a Comment on Payment Latency for Specified Payor Number 
In order to determine how many days it takes a payor to pay your claims, you
can examine firstbilled and paiddate values in JChgSplits. The following
query will tell you these dates and compute the number of days between them.
Just change the payor number (get the payor number from the second column in
Lookups > Ins Carriers/Plans) and the desired service date range in the
WHERE clause.
SELECT 
   a.jnum,c.amount AS "Fee",
   a.chgsplamt AS "SplitToPayor",
   a.firstbilled,
   a.paiddate,
   datediff(day,a.firstbilled,a.paiddate) AS "PaymentDays"
FROM 
   sos.jchgsplits a
   JOIN sos.ptpayors b ON a.ptpayornum = b.ptpayornum
   JOIN sos.journal c ON a.jnum = c.jnum
WHERE 
   b.payornum = 101
   AND c.trandate BETWEEN '2004-1-1' AND '2004-12-31'

Scheduler Reminders

Posted on | by SOS Blog | Leave a Comment on Scheduler Reminders

“Is there an easy query that would simply print all the scheduler reminders off the patient info form p.2?”

SELECT 
   LastName, 
   FirstName, 
   ID, 
   NoSchedReason
FROM 
   sos.Patients
WHERE 
   Flag = 0
ORDER BY 
   LastName, 
   FirstName
The Flag condition limits to active patients. If you want only those that have a reminder entered, add another condition. 
Note that the '' at the end of line three is two apostrophes, not a single quotation mark.
SELECT 
   LastName, 
   FirstName, 
   ID, 
   NoSchedReason
FROM 
   sos.Patients
WHERE 
   Flag = 0 
   AND NoSchedReason <> ''
ORDER BY 
   LastName, 
   FirstName
 Finally, the one below combines the name and id into a single field to make the output a bit cleaner:
SELECT 
   LastName+', '+ FirstName+' / '+ ID AS "Patient", 
   NoSchedReason
FROM 
   sos.Patients
WHERE 
   Flag = 0 
   AND NoSchedReason <> ''
ORDER BY 
   LastName, 
   FirstName

Collections by Service for Period

Posted on | by SOS Blog | Leave a Comment on 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

	


	





	

			

	

		
Last Payments
		

			Posted on  |  by SOS Blog |  Leave a Comment on Last Payments		

			

	

		
You asked for a report that includes last payment information. This information is readily available in the
database, but we have never included it in any of the standard reports.




SELECT
   (a.Lastname + ', ' + a.Firstname) AS "PtName",
   a.id,
   TRIM(c.firstname + ' '+c.payorname) AS "Payor",
   b.lastpaydate,
   b.lastpayamt,
   b.postedbal
FROM
   sos.patients a
   JOIN sos.ptpayors b ON a.ptnum = b.ptnum
   JOIN sos.payors c ON b.payornum = c.payornum
WHERE
   postedbal > 0