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
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"
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
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
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:
Click the indicated “Submit a ticket” link to open a ticket form, which looks like this:
Be 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.
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.
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";
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";
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