Category: G4 Queries

Mailing Labels for Active Patients with Zero Balance

Posted on | by SOS Blog | 2 Comments on Mailing Labels for Active Patients with Zero Balance

I would like to send a flyer to all patients who have no balance and who have been seen within the last six months.

The following query specifies only that the patient has been seen for some sort of service in the last 180 days and that the current balance is zero or less.

Check the appropriate How-To document in the blue bar at the top of the page for instructions on saving the results to HTML format that you can open and save with Excel. You can then use the Mail-Merge feature in MS Word to create your labels using this data. See Mail Merge Labels in Word Using Query Results.

SELECT
   (a.firstname + ' '+a.lastname) AS "name",
   a.addr1 AS "addr1",
   a.addr2 AS "addr2",
   (a.city+', '+a.state+' '+a.zip) AS "addr3"
FROM
   sos.rv_patients a
   JOIN sos.pt_noninsbalance  b ON a.ptnum = b.ptnum
WHERE
   b.ptbalance <= 0
   AND lfeedate > (TODAY()-180)
; OUTPUT TO c:\sos\labels.html FORMAT HTML

 

Detailed and Summary Views of Cash Receipts for a Period

Posted on | by SOS Blog | Leave a Comment on Detailed and Summary Views of Cash Receipts for a Period

If I am looking back at total credits by cash over a 10 year period and I wanted specifics on when the cash was posted and what the client name is that we received and posted cash to there account…how do I go about getting that?

See the appropriate “How-To” above for instructions about how to output the results to Excel or to a form you can print.

SELECT DISTINCT
  lastname +', '+ firstname +' / '+ "id" AS "Client Name", 
  srv_date AS "Date of Service", 
  cre_date AS "Date of Payment", 
  cre_amount AS "Amount Paid",
  j.daybatch AS "Daysheet"
FROM 
  sos.rv_creditsplits cs
  JOIN sos.journal j ON cs.srv_jnum = j.jnum
WHERE
 cs.licnum = 101
 AND credtype = 'cash'
 AND cre_date BETWEEN '1999-01-01' AND '2011-12-31'
ORDER BY
 "Client Name","Date of Payment","Date of Service","Amount Paid"

For the grand total, use this query:

SELECT 
  SUM(amount) 
FROM 
  sos.rv_credits c
WHERE
  licnum = 101 
  AND  credtype = 'cash'
  AND trandate BETWEEN '1999-01-01' AND '2011-12-31'

Insurance Services by Carrier for Period

Posted on | by SOS Blog | Leave a Comment on Insurance Services by Carrier for Period

We looking for a report that will list all sessions that have been billed for each insurance company during a month. For example, the report would list out many sessions have been billed for MVP insurance for the month of October. Please let me know if this is possible.

In the following query, the output is directed to an HTML file that can be viewed in a web browser, or loaded in Excel for additional manipulation.

SELECT DISTINCT
  pay.payorname, 
  pt.lastname, pt.firstname, pt.id, 
  jou.trandate, srv.srvcode, prv.provcode, jou.amount
FROM 
  sos.journal jou 
  JOIN sos.jcharges chg ON jou.jnum = chg.jnum
  JOIN sos.jchgsplits chs ON chg.jnum = chs.jnum
  JOIN sos.ptpayors ptp ON chs.ptpayornum = ptp.ptpayornum
  JOIN sos.payors pay ON ptp.payornum = pay.payornum
  JOIN sos.patients pt ON jou.ptnum = pt.ptnum
  JOIN sos.services srv ON chg.servicenum = srv.servicenum
  JOIN sos.providers prv ON chg.providernum = prv.providernum
WHERE
  pay.payortype = 'I'
  AND trandate BETWEEN '2011-11-01' AND '2011-11-30'
ORDER BY
  pay.payorname, pt.lastname, pt.firstname, pt.id, jou.trandate ;
OUTPUT TO c:\sos\insforperiod.html FORMAT HTML

Patients with Primary Provider and Primary Dx, Sorted by Provider Code

Posted on | by SOS Blog | Leave a Comment on Patients with Primary Provider and Primary Dx, Sorted by Provider Code

This query demonstrates the use of IF expressions to display “None” rather than “NULL” wherever there is no primary provider or dx.

SELECT 
  Patients.LastName, 
  Patients.FirstName, 
  Patients.ID, 
  (IF Patients.ProviderNum IS NULL 
  THEN 'None' 
  ELSE (Providers.ProvCode +':'+ Providers.ProvLName +', ' + Providers.ProvFName) 
  END IF) AS "Provider", 

  (IF ptcsudx.DxCode1 IS NULL THEN 'None' 
  ELSE ptcsudx.DxCode1 
  END IF) AS "PrimaryDx" 

FROM 
  sos.Patients 
  LEFT OUTER JOIN sos.Providers ON Patients.ProviderNum = Providers.ProviderNum 
  LEFT OUTER JOIN sos.PtCSU ON Patients.PtNum = PtCSU.PtNum 
  LEFT OUTER JOIN sos.ptcsudx ON PtCSU.ptcsunum =ptcsudx.ptcsunum 
WHERE 
  Patients.LicNum = 101 
  AND Patients.Flag = 0 
  AND Patients.DischargeDate IS NULL 
  AND PtCSU.TypeFlag = 'D' 
ORDER BY 
  Providers.ProvCode,Patients.LastName,Patients.FirstName,Patients.id 
;
OUTPUT TO c:\sos\provptdxlist.html FORMAT HTML

Patients by Insurance Carrier and Group Number

Posted on | by SOS Blog | Leave a Comment on Patients by Insurance Carrier and Group Number

Is there a patient report that lists all patients by insurance group number?

 

SELECT 
  a.payorname,a.insgroup,b.lastname,b.firstname,b.id
FROM 
  sos.rv_policies a JOIN sos.patients b ON a.ptnum = b.ptnum
WHERE 
  today() BETWEEN COALESCE(a.active,'1900-01-01') AND COALESCE(a.inactive,'2100-12-31')
ORDER BY 
  payorname, insgroup, b.lastname, b.firstname, b.id