SQL Tips on UDR Writing
By Nick Briseno

Need some SQL hints for UDR (User Defined Reports) report writing? Suppose you need to make data base updates for records that are created on a UDR. This will require some advanced SQL commands to be executed with the report. Take for instance the following example: you run a daily report that requires printing records that have been signed off and coded through CPT and ICD coding modules within syngo^®.

First validate the workflow of professional billing. If the billing staff is using syngo’s ICD and CPT coding modules, there are field updates that are recorded in the activity_info table that could later be queried onto a UDR. There is also a coded_dtime field and a coded_flag field related to what has been coded. Another use is activity_info, which also has other available fields that aren’t typically used by the system with prefix of bill_* billing_*, etc. These are indeed updatable fields.

Because many billing teams work with backlogs, often, records that have been signed off today will not be coded by tomorrow. Therefore the better logic is to run your UDR for at least 30 days back, to capture items that are coded. CPT and ICD coding does not occur until patient results have been signed off. Signoff makes the report official and eligible for billing.

The UDR is executed with signoff date parameters of T-30 to T-1 23:59. Meaning, today minus 30 days and up to today minus 1 day at 23:59 hours, or midnight. This allows for the capture of all items that have been signed off and coded in the past 30 days. However, since the report is running daily, overlap is an obvious risk. In order to avoid records that were previously billed, a flag or some other indicator will need to be used as part of the filtering process in the UDR. In this case, we’ll use the billing flags as the indicator(s). The following illustration shows the updatable fields that can be flagged to indicate a record has been reported.

Keep in mind, some of these fields are not available in all versions of syngo. The system in the illustration is a V27.2 system. V28 and more recent versions have additional fields available in activity_info, such as: coded_dtime, code_inits, etc.

To update these fields through a UDR, you would need code similar to the following example to be placed at the end of all the logic contained in the report.

<<
select

activity_info

update

activity_info.prof_bill_flag=”BILLED”, -- Flag activity_info.prof_bill_init=”NB” -- User defined activity_info.prof_bill_dtime=(getdate()), -- System date and time activity_info.bill_cmnt1=”BILLED ON:”+(getdate()), – Comments activity_info.bill_cmnt2=”REPORTED”

where

activity_info.acc_itn=@acc_itn

>>

Using these specific fields in the database will now provide flags for filtering during the next iterations of the report running. Further, keep in mind that the activity_info table cannot take any INSERT type commands. INSERT commands are to write actual records that are unique to the DB. Activity_info table contains very unique elements with acc_itn, which is the accession number assigned by the system.

The possibilities for using these types of commands are endless. “usr_flds” type tables can generally take INSERT commands, since they’re user defined tables, unless Interactive Documents are are heavily used. Even then, there are still a few options available.

During future reports running, the where clause filters for all billed items as the following:

Select

Variables

From

Tables

Where

Table.billing_flag IN (NULL, “”)

This type of filter, will exclude from generating an entry on future generated reports. That is, anything that has previously generated on a past report, will not longer post to current or future reports. Since the query is requesting only items that are not flagged in the billing_flag, future reports will not write items that have billing_flag valued.

Stay tuned for future SQL hints and tips for UDR users! But if you just can’t wait for the next newsletters call us at 610.444.1233 or email us at vcs@getvitalized.com. You can also read more about VCS’ services and solutions at www.getvitalized.com.