SOS G5 Backup and Recovery

Most SOS G5 organizations will be using Microsoft SQL Server to store their data. This database engine is available in a wide range of versions. Most SOS users probably will be using the free SQL Server Express edition, SQL Server Standard, or perhaps, SQL Server Enterprise. All three of these editions share the same core functionality and may be managed using Microsoft’s user interface known as SQL Server Management Studio (SSMS). Assuming that you have the rights to do so, you will be able to use SSMS to manage the database, including backing up and restoring the SOS G5 database. Again assuming that you have the necessary rights, if you are on a network, you will be able use SMSS to do these tasks from any workstation.

Typically, when you install SQL Server on your computer or server, you will also install a copy of SSMS. If you cannot locate a copy, want a copy on another computer, or want to update a copy you previously installed, you can download the installer for the current SSMS software at this Microsoft page: Download SQL Server Management Studio (SSMS).

Using SSMS to create a full backup of your database manually

Once you have installed your SOS G5 software and you have begun to enter data or have imported data from the previous version of SOS, you should backup that data as soon as you have the opportunity. As a start, let’s use SSMS to create a manual backup. We will then make a copy on external media as well.

  • Start by opening SQL Server Management Studio (SSMS).

Once you have logged in, click the plus to the left of “Databases” to expand that node. Right-click the SOSSuite database, then click “Tasks”, then click “Back Up…”.

The dialog for the backup task includes three panels. Go from one to the other using the navigation panel on the left:


Automating database backups using SQLCMD and Windows Scheduled Tasks

Thankfully, it is not necessary to manually go through the steps above every day (or more often). Instead, you can use Windows’ Task Scheduler to automatically run commands to create backups of the database and the transaction log in any desired location on the computer running SQL Server. For example, you could put the following two command lines in a file named BACKUP_SOSSUITE.CMD, and configure Task Scheduler to run this CMD file every night at 1:00 AM. Here are the two commands. The portions that would have to be changed to match your particular installation are in bold.

(all on one line) SQLCMD -E -S .\SQLEXPRESS -Q “BACKUP DATABASE[SOSSUITE] TO DISK=’C:\BACKUPS\SOSSUITE-DATABASE.BAK‘” > C:\BACKUPS\BackupDatabaseResults.txt

(all on one line) SQLCMD -E -S .\SQLEXPRESS -Q “BACKUP LOG[SOSSUITE] TO DISK=’C:\BACKUPS\SOSSUITE-TRANLOG.BAK‘” > C:\BACKUPS\BackupDBLogResults.txt

In this example, .SQLEXPRESS is the name of the SQL Server instance that is running the SOSSuite database. After DISK= is the desired full path and filename of the database or transaction log backup file, and after “>” is the name of the file that is used to capture the results of each of the commands so that you can check to be sure that the backups ran successfully.

Creation of these BAK files IS NOT A SUFFICIENT BACKUP. By definition, these files will be created on a local drive, perhaps even the same drive as your database is running. In addition, every time the commands are run, these files will be replaced by the latest ones. That means that an equipment failure, fire, or theft could wipe out both your data and your only backup!

For that reason it is essential that you include these BAK files in your daily computer or server backups to external media, using third-party backup software such as NovaStor’s NovaBackup or Cloudberry Lab’s Cloudberry Backup products, among many others. High-end server backup software often includes the ability to backup SQL Server databases directly, without the intermediate steps described above, which would certainly be an excellent option. Your backup software will have options to encrypt your backups, which is absolutely essential, and to compress them to minimize the amount of space consumed on your backup media.

See also:

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-2017#syntax

Find Patients with No Recent Activity

I would like to develop one I can use prior to record destruction.  The information I want is: list of client names, last name then first (all active andinactive), date of birth, age (the day of query run), first date seen, last date seen, last transaction, last payment, all providers that rendered service, and patient category (if assigned).  I think this sounds complicated.  Can it be done in one query?  Would first date seen pull from intake date or first transaction or first charge?  Would last date seen pull from last charge, discharged date, or last transaction?

In the query below, “last transaction” is the most recent entry of any kind in the patient’s ledger. If more than one provider rendered services to the patient, the results show a line for each. The “first seen on” and “last seen on” dates are specific to the provider shown on that line, but the “last payment” and “last transaction” are not provider-specific.

This query features a couple of functions that we created here at SOS and added to the library of functions available in SQL Anywhere. AGEINYEARS returns an accurate calculation of age, taking leap years into consideration. LASTCREDITDATE examines the patient’s ledger and returns the date of the last actual payment — that is, it ignores adjustment credits.

The WHERE clause filters the results by data set (licnum), includes both active and inactive patient lists, and returns only those patients with a last transaction date at least 180 days ago. Of course, you can modify each of those values for your specific needs.

Also of interest is that the SELECT list of this query consists mostly of subqueries. Using subqueries in this fashion often results in much better performance, even when the same results can be achieved with additional joins in the main query. As shown here, each subquery must be within a set of parentheses and be followed by “AS <your column name>”.

SELECT DISTINCT
  a.lastname + ', ' + a.firstname AS "Name",
  a.id AS "Account ID",
  a.dob AS "DOB",
  sos.AGEINYEARS(a.dob,today()) AS "Age",
  ( SELECT provcode FROM sos.providers WHERE providernum = b.providernum) AS "Provider",
  ( SELECT MIN(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "First Seen On",
  ( SELECT MAX(trandate) FROM sos.journal jou JOIN sos.jcharges chg 
    WHERE jou.ptnum = a.ptnum AND chg.providernum = b.providernum AND trantype = 'S' AND amount > 0) AS "Last Seen On",
  ( SELECT sos.LASTCREDITDATE(a.ptnum)) AS "Last Payment",
  ( SELECT MAX(trandate) FROM sos.journal WHERE ptnum = a.ptnum) AS "Last Transaction",
  ( SELECT categcode FROM sos.ptcategs WHERE ptcategnum = a.ptcategnum) AS "Pt Category"
FROM 
  sos.patients a
  LEFT OUTER JOIN sos.jcharges b ON a.ptnum = b.ptnum
WHERE
  a.licnum = 101  //specifies the dataset to be examined
  AND a.flag IN(0,1)  //includes both active (0) and inactive (1) patient lists
  AND "Last Transaction" < (TODAY() - 180)  //show only those whose last transaction was 180 days ago
ORDER BY "Name","Account ID","Provider"

Fees and Payments for Period, Grouped by Patient Category

For multiple patient categories, we would like totals billed AND collected for a specified date range.

The following query provides the desired totals, but it is important to note that the payment total is a total of any payments received during the period, not just payments for services rendered during the period. In other words, the payments probably include those for previous services and maybe even some prepayments. Also note that we are using SPLIT amounts for both charges and credits, so if you have corruption that makes the sum of the splits on a transaction different than the transaction amount, the total won’t match reports or queries that use the main amount field.

The following is actually TWO similar queries, one for payments and one for fees, with the use of  UNION to combine the results of each query into a single result set. UNIONed queries must not have their own ORDER statement. Instead, at the end you can specify an ORDER BY followed by the number of the column on which you want to sort, as shown below.

SELECT 
  c.categcode AS Category 
  ,'Payments' AS "Type"
  ,SUM(COALESCE(b.crsplamt,0)) AS "Total"
FROM 
  sos.patients a 
  JOIN sos.rv_creditsplits b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.credtype IN ('CASH','CHECK','CHARGE','OTHER')
  AND b.cre_date BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode

UNION

SELECT 
  c.categcode AS Category 
  ,'Fees' 
  ,SUM(COALESCE(b.chgsplamt,0)) 
FROM 
  sos.patients a 
  JOIN sos.rv_charges b ON a.ptnum = b.ptnum 
  JOIN sos.ptcategs c ON a.ptcategnum = c.ptcategnum
WHERE 
  a.licnum = 101
  AND b.trandate BETWEEN '2000-01-01' AND '2014-12-31'
GROUP BY 
  c.categcode
ORDER BY 
  1,2

Average Number of Visits by Year, Month, and Provider

 I’m looking for a query that tells me the AVERAGE number of treatment sessions per patient (as defined by a service code whose “include on claims” box is checked in SERVICES under LOOKUPS) for a date range.  I’d prefer that the query not list every patient but an average of each provider’s patients.
 
SO, given our 75 providers, what is each providers average amount of sessions conducted per patient over a 4 week period. If a provider saw every patient once a week, their average would be 4.  If another provider saw every patient every two weeks, their average would be 2.

Interesting question and (mostly) answered by a simple query. Note that there is built-in error due to mid-month intakes, but if we assume that variable is relatively stable, then this should give you the trend you want.

The data is output by year, month, and provider code. You can set the range to be examined by altering the date range in the WHERE clause. Instead of a plain GROUP BY, this query uses GROUP BY ROLLUP to provide  subtotals. Remember that where you see NULL in the output, read it as “All”. The ROLLUP will then give you averages for the entire period, each year, and each month, as well as by provider and across providers:

 

SELECT   
  YEAR(j.trandate) AS yr,MONTH(j.trandate) AS mon,
  p.provcode,
  count(distinct j.ptnum) AS pts,
  count(distinct j.jnum) AS visits,
  visits/pts AS average
FROM
  sos.journal j 
  JOIN sos.jcharges c ON j.jnum = c.jnum
  JOIN sos.providers p ON c.providernum = p.providernum
  JOIN sos.services s ON c.servicenum = s.servicenum
WHERE
  j.trandate BETWEEN '2000-01-01' AND '2012-12-31'
  AND s.insbillable = 1
GROUP BY ROLLUP
  (yr,mon,provcode)
ORDER BY
  yr,mon,provcode

Service Count and Units By Patient Category, Provider Type, and Service Code

We need a report that provides units provided/charges for a specified patient category, broken down by provider type and service code for a specified date range from both active and inactive clients in dataset 101.

Be sure to adjust the date range in the WHERE clause for your desired period.

This query uses the GROUP BY ROLLUP (  ) statement to give grand and subtotals as well as the results for each specific patient category, provider type, and service code combination. Wherever you see NULL in the result set, interpret as “ALL”.

SELECT
  COALESCE(d.categcode,'None') AS "PtCategory",
  COALESCE(f.provtypecode,'None') AS "ProvType",
  g.srvcode AS "ServiceCode",
  COUNT(distinct b.jnum) AS ServiceCount,
  SUM(b.units) AS TotalUnits
FROM
  sos.journal a
  JOIN sos.jcharges b ON a.jnum = b.jnum
  JOIN sos.patients c ON a.ptnum = c.ptnum
  LEFT OUTER JOIN sos.ptcategs d ON c.ptcategnum = d.ptcategnum
  JOIN sos.providers e ON b.providernum = e.providernum
  JOIN sos.services g ON b.servicenum = g.servicenum
  LEFT OUTER JOIN sos.provtype f ON e.provtypenum = f.provtypenum
WHERE
  a.trandate BETWEEN '2012-01-01' AND '2012-12-31'
  AND a.amount > 0
  AND c.licnum = 101
GROUP BY ROLLUP ("PtCategory","ProvType","ServiceCode")