Category: Queries – Patients

 How do I calculate current ages? I am trying to create a list of active patients 18 and younger with their ages and birthdays.

You might think that you could use the standard SQL function called DATEDIFF to just subtract the years, but that won’t give you an accurate age if the current date is earlier in the year than the birthday. To get a true age calculation, use the custom function “AGEINYEARS” that SOS has added to your database. It takes two parameters, the date of birth and the reference date on which you want to calculate age. Most of the time that will be CURRENT DATE, but you can use a different date if desired.

SELECT
  lastname  AS "Last Name",
  firstname AS "First Name",
  id,
  sos.AGEINYEARS(dob,CURRENT DATE) AS "Current Age" ,
  DATEFORMAT (dob, 'MM-DD-YYYY') AS "Date of Birth"
FROM
  sos.patients
WHERE
  flag = 0  /* active patients only */
  AND dischargedate IS NULL    /* no discharge date has been entered */
  AND "Current Age" <= 18  /* only patients younger than 18 years of age */
ORDER BY 
  lastname,firstname, id

The following query will create an Excel file suitable for Mail Merge use. The results will have information from the “Bill To” tab of patient information, if present, otherwise will use the patient information. It does not pull information from alternate payors on the
account. The file generated is in HTML format, but you can open it in Excel and re-save as an Excel format file.

SELECT
  TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
  TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
  TRIM("firstname" + ' ' + "lastname") AS "FullName",
  TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
  TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
  TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
  TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
  TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
  sos.patients a JOIN sos.payors b ON a.payornum = b.payornum
WHERE
  a.flag = 0    /* only patients in active list */
  AND a.licnum = 101    /*only patients in the main data set*/
;
OUTPUT TO c:\sos\billingaddresses.html FORMAT HTML

Let’s say that you just want addresses for patients who have been seen for billable services in the past two years (730 days). There is a column in the ptvars view called “lfeedate” that has the date of the last billable service. So we can add ptvars to the FROM clause, and the lfeedate to the WHERE clause, as shown below:

SELECT
  TRIM(IF a.billtofirstname > '' THEN a.billtofirstname ELSE a.firstname ENDIF) AS "FirstName",
  TRIM(IF a.billtolastname > '' THEN a.billtolastname ELSE a.lastname ENDIF) AS "LastName",
  TRIM("firstname" + ' ' + "lastname") AS "FullName",
  TRIM(IF a.billtoaddr1 > '' THEN a.billtoaddr1 ELSE b.Addr1 ENDIF) AS "AddressLine1",
  TRIM(IF a.billtoaddr2 > '' THEN a.billtoaddr2 ELSE b.Addr2 ENDIF) AS "AddressLine2",
  TRIM(IF a.billtocity > '' THEN a.billtocity ELSE b.City ENDIF) AS "City",
  TRIM(IF a.billtostate > '' THEN a.billtostate ELSE b.State ENDIF) AS "State",
  TRIM(IF a.billtozip > '' THEN a.billtozip ELSE b.Zip ENDIF) AS "Zip"
FROM
  sos.patients a
  JOIN sos.payors b ON a.payornum = b.payornum
  JOIN sos.ptvars c ON a.ptnum = c.ptnum
WHERE
  a.flag = 0    /* only patients in active list */
  AND a.licnum = 101    /*only patients in the main data set*/
 AND c.lfeedate > (CURRENT DATE - 730) /* service within past two years */
;
OUTPUT TO c:\sos\billingaddresses.html FORMAT HTML