List Cities with Number of Patients Seen

This query produces a list of cities and the number of patients in each one who have been seen during a specified date range.

SELECT b.city, count(distinct a.ptnum) as "Count"
FROM patients a JOIN payors b ON a.payornum = b.payornum
WHERE a.ptnum IN (SELECT ptnum FROM journal 
                  WHERE trantype = 'S' 
                  AND trandate BETWEEN '2012-11-01' AND '2013-06-30')
GROUP BY b.city
ORDER BY b.city

Patients and all ICD-10 Dx Codes in Professional Claim Setup(s)

This is a very simple query that lists all active patients along with all ICD-10 diagnoses in their professional claim setups.

 

SELECT 
  pt.id AS "ID",
  pt.lastname AS "Last Name", 
  pt.firstname AS "First Name",
  pt.dob AS "Date of Birth",
  pt.socsec AS "SSN",
  dx1.dxcode AS "Dx1",
  dx2.dxcode AS "Dx2", 
  dx3.dxcode AS "Dx3", 
  dx4.dxcode AS "Dx4", 
  dx5.dxcode AS "Dx5", 
  dx6.dxcode AS "Dx6", 
  dx7.dxcode AS "Dx7", 
  dx8.dxcode AS "Dx8", 
  dx9.dxcode AS "Dx9",
  dx10.dxcode AS "Dx10",
  dx11.dxcode AS "Dx11",
  dx12.dxcode AS "Dx12" 
FROM 
  patients pt
  JOIN ptcsu on pt.ptnum=ptcsu.ptnum
  LEFT OUTER JOIN dx dx1  on ptcsu.icd10dx1=dx1.dxnum
  LEFT OUTER JOIN dx dx2  on ptcsu.icd10dx2=dx2.dxnum
  LEFT OUTER JOIN dx dx3  on ptcsu.icd10dx3=dx3.dxnum
  LEFT OUTER JOIN dx dx4  on ptcsu.icd10dx4=dx4.dxnum
  LEFT OUTER JOIN dx dx5  on ptcsu.icd10dx5=dx5.dxnum
  LEFT OUTER JOIN dx dx6  on ptcsu.icd10dx6=dx6.dxnum
  LEFT OUTER JOIN dx dx7  on ptcsu.icd10dx7=dx7.dxnum
  LEFT OUTER JOIN dx dx8  on ptcsu.icd10dx8=dx8.dxnum
  LEFT OUTER JOIN dx dx9  on ptcsu.icd10dx9=dx9.dxnum
  LEFT OUTER JOIN dx dx10  on ptcsu.icd10dx10=dx10.dxnum
  LEFT OUTER JOIN dx dx11  on ptcsu.icd10dx11=dx11.dxnum
  LEFT OUTER JOIN dx dx12  on ptcsu.icd10dx12=dx12.dxnum
WHERE 
  pt.flag = 0
ORDER BY 
  lastname,firstname,"ID"

 

Adjustments Made in 2016 for Services Rendered in 2015

Need a query which lists:
Patient Name (lname, fname), ID, DOS, Date of Adjustment, Amount of adjustment, Payor Category (patient, insurance or other) for:

  • Any patient with 2015 date of service who has any adjustment made in 2016.
  • some patients will have adjustments in 2015 but I only need to see those patients and DOS with adjustments made in 2016
  • some patients will have one DOS with multiple adjustments, that’s OK, each adjustment and date of adjustment should show on a separate line.

This query illustrates the use of a CASE expression and the date function YEAR.

 

SELECT
  lastname,
  firstname,
  "ID" ,
  srv_date,
  cre_date AS adj_date,
  crsplamt AS adj_amt,
  adjcode,
  (CASE 
     WHEN payortype = 'I' THEN 'Insurance'
     WHEN payortype = 'S' THEN 'Patient'
     ELSE 'Other'
   END) AS 'Payer Category'
FROM
  rv_creditsplits
WHERE
  credtype = 'adjustment'
  AND YEAR(srv_date) = 2015
  AND YEAR(cre_date) = 2016 
ORDER BY
  lastname,firstname,"id",srv_date

How-To Request Help through the On-line Help Desk

Introduction

Did you know that your current Support/Update agreement allows you to get support for SOS products by sending an email or by filling in a request on the SOS Help Desk website? Both of these techniques will create a new service ticket at the Help Desk, and a notification that a ticket is waiting automatically goes out to all support staff.

Emailing a request

Simply address your request to “support@sosoft.com”. Please include a brief description of your issue. (Note that emailing a specific person at SOS is not a good idea. That person could be out of the office or busy with another project and not see your email for a while. Using the Help Desk is the best way to get a prompt response.)

Creating a ticket on the HelpDesk website

    1. Open your web browser and go to http://help.sosoft.com. (You will automatically be redirected to the Help Desk address, which is actually https://sosoft.us/hesk.) Here is what it looks like:

      2016-09-13-help-desk
    2. Click the indicated “Submit a ticket” link to open a ticket form, which looks like this:

2016-09-13_submit-ticketBe sure to fill in all the required fields (indicated by an asterisk). It is helpful to us if you also fill in your phone number and/or the licensee name that appears at the top of your SOS product’s main screen. If you want to send a screenshot or any other file, you can do so in the Attachments section of the form, but be sure to check anything you are sending to assure it does not include any patient information like names, phone numbers, diagnoses, services rendered or other protected health information (PHI).

. . . be sure to check anything you are sending to assure it does not include any patient information like names, phone numbers, diagnoses, services rendered or other protected health information (PHI).

3. When you have completed the form, click the “Submit Ticket” button at the bottom of the screen to send it. When an SOS staffer has responded, you will receive an email notification at the address you left in your ticket.

Payment Breakdown Precentages by Provider and by Payer

I am looking for a report that will show me the % of each insurance payer seen each month as well as to have the ability to break that number down by % of insurance payer seen by individual providers each month. For example, United health care is what % or my business in the month of July.

If we were to simply look at amounts rather than percentages, this request could be met by a pretty simple query, using the ROLLUP feature:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'
  AND  a.credtype IN ('check','cash','charge')
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Payer","Provider")
ORDER BY 
  "Payer","Provider";

The results would look like the figure below. “(NULL)” should be read as “All”, so the first line is “All payers and all providers paid 658,699.78.” The second line is “Payer: AARP (2) for all providers paid $732.28.” The next three lines show the amount paid by the same payer for each of the three providers, 006, AT, and PPP.

Q1

The need for a percent, however, means that we must have the relevant total always available for the current payer. That means we will have to insert a subquery in the SELECT list, either as a displayed column or as part of a much more complex expression used to calculate the percentage. For readability sake, let’s separate out the calculation of the total into a “PayerTotal” column of its own. We can now also add a Percent column that is simply the Paid amount divided by the PayerTotal, multiplied by 100.

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  SUM(a.crsplamt) AS "Paid",
  (SELECT SUM(crsplamt) FROM sos.rv_creditsplits   
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
         AND credtype IN ('check','cash','charge')
         AND licnum = 101 
         AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer")  AS "PayerTotal", 
  ( ("Paid"/"PayerTotal") * 100) AS "Percent" FROM sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
  AND  a.credtype IN ('check','cash','charge') 
  AND a.licnum = 101    /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider")
  ORDER BY "Payer","Provider";

Q2

These changes give us pretty much what we want, but we can add some code to round the percent values to two decimal places. In addition, we should add an IF condition to prevent a divide-by-zero error if the PayerTotal is zero. The final query becomes:

SELECT
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  a.provcode AS "Provider",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
      AND credtype IN ('check','cash','charge') 
      AND licnum = 101 
      AND (IF payortype = 'I' THEN payorname ELSE 'NonIns' ENDIF) = "Payer") AS "PayerTotal",
  SUM(a.crsplamt) AS "Paid", 
  (IF PayerTotal > 0 THEN CAST( (("Paid"/"PayerTotal") * 100) AS DECIMAL(6,2)) ELSE 0 ENDIF) AS "Percent" 
FROM 
  sos.rv_creditsplits a 
WHERE 
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31' /*desired date range*/ 
  AND a.credtype IN ('check','cash','charge') /*only payments - no adjustments*/ 
  AND a.licnum = 101 /*only patients in the main data set*/ 
GROUP BY 
  ROLLUP("Payer","Provider") 
ORDER BY "Payer","Provider";

Q3

It is important to note that to effectively change the date range for the query, you must change the date range in BOTH the main query and the subquery.

If you prefer percentages for each provider rather than for each payer, that can be accomplished with just a little editing of the subquery, the GROUP BY and the ORDER BY, like so:

SELECT
  a.provcode AS "Provider",
  (IF a.payortype = 'I' THEN a.payorname ELSE 'NonIns' ENDIF) AS "Payer",
  (SELECT SUM(crsplamt) 
   FROM sos.rv_creditsplits 
   WHERE cre_date BETWEEN '2000-01-01' AND '2016-12-31' 
     AND credtype IN ('check','cash','charge')
     AND licnum = 101 
     AND provcode = "Provider")  AS "ProvTotal",
  SUM(a.crsplamt) AS "Paid",
  (IF "ProvTotal" > 0 
     THEN CAST( (("Paid"/"ProvTotal") * 100) AS DECIMAL(6,2)) 
     ELSE 0 
     ENDIF) AS "Percent"
FROM
  sos.rv_creditsplits a
WHERE
  a.cre_date BETWEEN '2000-01-01' AND '2016-12-31'  /*desired date range*/
  AND  a.credtype IN ('check','cash','charge')     /*only payments - no adjustments*/
  AND a.licnum = 101    /*only patients in the main data set*/
GROUP BY 
  ROLLUP("Provider","Payer")
ORDER BY 
  "Provider","Payer";

The results are now percentages of provider totals rather than payer totals

2016-08-04_15-30-12