SQL Tip: Using MEDITECH Date Mnemonics in SSIS

Written by Thomas Harlan, Data Repository Technical Team Lead for Reporting Services, Iatric Systems

One of many nice things about the MEDITECH system is the availability of date mnemonics that you can use, either when running reports interactively, or when scheduling them. But this functionality is not available in SQL Server Reporting Services (SSRS) or in SQL Server Integration Services (SSIS) by default.

Fortunately, you can add that functionality by adding a custom function or stored procedure into your zcus database catalog, and then invoking the code from your SSRS report.

IatricDateInterpreterFN.sql

IatricDateInterspreterSp.sql

That covers reports – even scheduled/subscribed reports in SSRS – but what about SSIS?

In the integration services environment, if you want an extract to drop on a daily basis for yesterday’s data (for example, a daily file to a vendor), you have two general approaches you can take:

Approach One

You can set up two package variables that calculate the beginning and end of yesterday from expressions:

dRunFromDate = (DT_DATE) (DT_DBDATE)DATEADD("dd",-1,GETDATE())

dRunThruDate = DATEADD("s",-1, (DT_DATE) (DT_DBDATE)GETDATE())

The two calculated dates can then be passed to stored procedures or queries to get your data, and if the job is scheduled daily, you’ll always get yesterday’s data out.

Unfortunately, this isn’t terribly flexible. For example, if you want an extract to run for all of last month, you’d have to go into the package, edit the expressions on the calculated variables, and then run, or save-and-run, the package:

dRunFromDate =(DT_DATE)((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"-01")

dRunThruDate = DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-1,GETDATE()))))

We need something better than that!

Approach Two

To get more flexibility with scheduling or running the package ad-hoc, we bring in our date mnemonic code.

We keep our dRunStartDate and dRunEndDate variables, but we populate them using the date mnemonic code:

  1. Add two new string variables to your package: cFromDateMnemonic and cThruDateMnemonic.
  2. These variables are NOT expressions. They have values you set directly, like “MB-2” and “ME-2”.
  3. We use those variables, in conjunction with the function version of our date translator, to generate FROM_DATE and THRU_DATE values, which we then store into the dRunFromDate and dRunThruDate variables.
  4. To combine our new string variables into a query that executes our function code to generate the dates we want, we create another variable controlled by an expression: cDateMnemonicsQuery:

    "DECLARE @cFromDatePattern  VARCHAR(255) ;
    DECLARE @cThruDatePattern    VARCHAR(255) ;
      
    SET @cFromDatePattern = '"+ User::cFromDateMnemonic] +"' ;
    SET @cThruDatePattern   = '"+ @[User::cThruDateMnemonic] +"' ;

    SELECT  CONVERT(DATE,zcus.dbo.IatricDateInterpreterFn( @cFromDatePattern )) AS FROM_DATE
            ,CONVERT(DATE,zcus.dbo. IatricDateInterpreterFn ( @cThruDatePattern )) AS THRU_DATE
    ;"
     
  5. Then, we set up an Execute SQL task that runs our function twice, using cFromDateMnemonic and cThruDateMnemonic as parameters. This is what the Execute SQL task could look like, after we create it as the first task on the Control Flow of the SSIS package:

    ExecuteSQLDateMnemonics_Step1.jpg
    On the result-set pane, we assign the results of the SQL to our variables:

    ExecuteSQLDateMnemonics_Step2.jpg

  6. Then, we use those variables as parameters for our queries or stored procedures. 

But...

This leaves us a bit short, because we still have to open the package and edit the values in cFromDateMnemonic and cThruDateMnemonic to get an ad-hoc range for our package.

This brings us to SQL Server Integration Services 2012 (or better) and Package Parameters. If you happen to be on SQL 2012, you can get past this last little challenge by creating two Package Parameters – cFromDateMnemonic and cThruDateMnemonic – and then using the values in those parameters to drive your calculation of dFromDate and dThrudate.

First create the Parameters in the package:

ExecuteSQLDateMnemonics_Step3.jpg

Then, update your variable that builds the query to turn the mnemonics into DATETIMEs:

"DECLARE @cFromDatePattern  VARCHAR(255) ;
DECLARE @cThruDatePattern    VARCHAR(255) ;   

SET @cFromDatePattern = '"+ @[$Package::cFromDateMnemonic] +"' ;
SET @cThruDatePattern   = '"+ @[$Package::cThruDateMnemonic] +"' ;

SELECT  CONVERT(DATE,zcus.dbo.IatricDateInterpreterFn( @cFromDatePattern )) AS FROM_DATE
        ,CONVERT(DATE,zcus.dbo. IatricDateInterpreterFn ( @cThruDatePattern )) AS THRU_DATE
;"

The result is:

  1. Parameters are set at the package level.
  2. Parameters are passed into the package, and converted by an Execute SQL Task into variables.
  3. The variables are then used in the queries to set the date range.

Once You’ve Scheduled the SSIS package…

One of the fantastic things about Package Parameters is that when you schedule them in a SQL Agent Job, you can set them at the job level to override what is defined in the package.

That lets you manipulate the Package Parameters in DataTools 2012+ for testing, then schedule the job, and set it to whatever regular pattern you want (T-1 to T or MB-1 to ME-1 or whatever).

Once an override is set in the SQL Agent job, it will “stick,” even if you update the parameter values in the SSIS package and redeploy it.

If you need more help…

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or Karen Roemer (karen.roemer@iatric.com) to discuss how we can help support your team!

RW-HELP-Button-Blue.png

Topics: Report Writing Services, Data Repository, SQL Tip, Report Writing Tips and Tricks

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE:

Comments

0 COMMENTS

SQL Tip: Using MEDITECH Date Mnemonics in SSIS

One of many nice things about the MEDITECH system is the availability of date mnemonics that you can use, either when running reports interactively, or when scheduling them. But this functionality is...Read More

SQL Tip: How to Remove MTDD-style Formatting from TextLines

This example is based on how discharge text data is formatted in the MEDITECH MAGIC Data Repository (DR). In the continuing saga of trying to recreate patient discharge instructions from the DR, I...Read More

Summer Webinars: Tips n Tricks, 6.1 Reports Migration, and More

At International MUSE in May, our report writing educational sessions were a hit once again. We presented two sessions: NPR and Report Designer Tips n Tricks 6.1 Reports Migration For some reason,...Read More

PHI and MEDITECH’s Data Repository

To address HIPAA, MEDITECH added logging of access to PHI via reports and all applications, and added that level of detail to MIS user activity logs. So, if you run an NPR, RD, or standard report and...Read More

NPR Tip: Various “Gotchas” in MEDITECH field names

What's in a name? In teaching NPR, RD, and SQL from DR, a big chunk of my time is spent helping students figure out where data is kept (for example, which segment in NPR), and then what field names...Read More

DR Tip: A Dynamic Extract Documentation Template

This blog post is designed for readers who use MEDITECH’s Data Repository. As part and parcel of developing ETL extracts for vendors, it is often quite useful to build a technical specification...Read More

SQL Tip: How to Strip Repeating Delimiters from String

This is a small tip, but could be quite useful on the days you need it. Particularly when you work with MEDITECH 6.x PCS data, you will often encounter query response values that look like this:...Read More

SSRS Tip: Adding a Pipe-delimited option to SSRS Export Destinations

End users are very fond of running reports in SSRS and exporting the results to a variety of formats. Excel is probably the most popular, but invariably there will be other formats needed....Read More

SQL Tip: The Good, the Bad, and the Ugly of TOP X

As you’re learning SQL, you will invariably come across the TOP X clause to a SELECT, which allows you to get – for example – the first 50 rows of a query: SELECT TOP 50 ADV.VisitID FROM...Read More

Migration: Export MOX Using NPR

Recently a MEDITECH hospital wanted to export a handful of MOX databases, since they were migrating away from MOX. I have done a few of these in the past on rare occasions (on a “per database”...Read More

Using DR Data Def Tables, Updated for CS/MAGIC 5.67 or MT 6.07/6.15

One of the challenges of DR-based report development is the same as for NPR or RD … where is the data you want stored? One way to find out is to go into MEDITECH and find the field, and — if you're...Read More

SSIS Tip: Formatting Numbers with Commas via Expression

Oh, Microsoft - why do you make us cry? And no, I am not talking about Windows 10. Though that does make me cry a bit. Every day. But enough about me. You (sound developer that you are) have built a...Read More

SQL+DR Tip: Use SSIS Templates to Speed Extract Builds

If you’ve started using Microsoft’s SQL Server Integration Services (SSIS) toolkit to create, schedule, and deliver extracts (machine-readable files) to vendors, non-MEDITECH applications, and even...Read More

SQL Tip: Managing Diagnosis Codes (5.67 edition)

A common challenge in reporting (or extracting data, in this case) that we see over and over again is the need to transform one data structure into another. The best arrangement of data for the...Read More

NPR Tip: Abstract Status Index (Client/Server)

A MEDITECH 6.0 customer recently requested our help to speed up a report she was using to find all accounts with a final abstract date of T-1. Her report, using the “abs.status.x” index was taking...Read More

MEDITECH DR Tip: Data Dictionary Search with Possessives

Some time ago we published a handy tip providing you with a stored procedure you could use to hunt for things in MEDITECH DR data structure(s) — by table name, by NPR DPM, etc. — so you could get...Read More

MEDITECH SSIS+SQL+DR Tip: Using CMS ICD-10 GEM data

CMS Does Not Make Things Easy Sometimes you just have to wonder… New ICD-10 diagnosis and procedural coding has been brought into play in the United Status (October 1, 2015 for diagnosis codes; next...Read More

NPR Tip: Client/Server “Echo Name” feature

In an earlier blog tip I showed how to add an “Echo Name” feature to a MAGIC NPR report, but I did not show a method to do the same thing for Client/Server because I assumed I’d need to use the...Read More

SQL Tip: Auto-printing from SSRS to a Networked Printer

This is probably our second-most requested tip – how do I emulate, from SSRS, the standard MEDITECH scheduled auto-print to a spooled printer functionality? A Bad Idea Before we show you how to...Read More

Testing Code for ICD-10

October 1 is coming fast, which means that this year (2015) US healthcare finally gets to join the rest of the world in using the ICD-10 codeset for diagnoses. MEDITECH is already in reasonable shape...Read More

SQL/SSRS/Data Repository tips, MEDITECH NPR Report Writer tips

Welcome to our Report Writing Blog! Welcome to the Iatric Systems Report Writing Blog. This blog will be the new home to our monthly SQL/SSRS/Data Repository Tips, MEDITECH NPR Report Writer Tips,...Read More

Save Trees and Charge for More ER Supplies

MEDITECH MAGIC or Client/Server – Force a report to PREVIEW and show bar codes for scanning A MEDITECH MAGIC customer recently asked us to produce a report that could show bar codes on the screen of...Read More

NPR Tip: "Echo Name" feature for Customer Report Screens (MAGIC Only)

You may have noticed that MEDITECH standard NPR report screens can have "display only" fields, but in customer reports, there is no attribute or standard method to provide the same feature. In a CDS,...Read More

DR+SQL Tip: Using UNION to Best Effect

MEDITECH migrations platform bring many challenges; one of them being that you may find yourself with a new LIVExDB in the picture and once you’re past the go-live you find that data is flowing only...Read More

DR+NPR Tip: Running Web-based Reports from a MEDITECH Menu

with Mitchell Lawrence, HIS Programmer Analyst at Iatric Systems Overview Many sites using SQL Server Reporting Services will use the External Links feature in MEDITECH to route a user to the "top"...Read More

SQL Tip - Managing Diagnosis Codes

(NOTE: This post only applies to hospitals operating MEDITECH 5.66 or lower. For hospitals operating MEDITECH 5.67 or higher, please refer to Thomas' March, 2016 post. Thank you.) A common challenge...Read More

NPR Tip:  What Happened on March 1st 1980?

MAGIC and Client/Server Time Stamps and how to use them. Example Reports Report with "elapsed time" information Export of Doctor Dictionary Entries created or edited on/after selected date. List of...Read More

SQL Tip —The Data Request Number

As we’ve discussed before in webinars, white papers and in our SQL training classes, we recommend that all DR-based reporting and extracting be driven by stored procedures. Those stored procedures be...Read More

NPR Tip: Every Module Can Have Room and Bed Index (MAGIC or Client/Server)

It is quite common to write reports for current inpatients, and a logical assumption by many NPR report writers is that such reports are best built in ADM, even when the data required is in some...Read More

NPR Tip: Keep Users Entertained During Download or Printing (MAGIC or Client/Server)

If you have a report that users are going to run and wait for, it can be nice to print some kind of progress message to the screen to keep them entertained. The easiest way to do this is to use the...Read More

SQL Tip -- Multi-value Parameters for SSRS

Continuing on from last month, when you start building DR + SSRS reports to replace or supplement NPR or RD reporting, you lose some conveniences from direct MEDITECH integration. One of them is the...Read More

SQL Tip — Facility Prompt by User Access

One of the challenges of moving from inside MEDITECH-integrated reporting (NPR or RD) outside into the realm of DR (SQL + SQL Server Reporting Services or Crystal Reports)- based reporting is that...Read More

Stop at Query if Patient is "on Coumadin" (MAGIC Only)

Someone from Mid Columbia Medical Center in Oregon asked for a way to stop at a query only if a patient was "on coumadin." I am going to interpret that to mean any active PHA order for Warfarin...Read More

SQL Tip -- Check Tables for Data

One of the challenges of the Data Repository (DR) is that we often need sets of tables populated with data and, despite everyone’s best efforts, when the rubber hits the road – one of them is empty....Read More

Getting Most Recent BAR Comment for Account (MAGIC, Client/Server, or 6.0)

A BAR analyst from a C/S hospital posted a request to the "Meditech-L" mailing list, asking for help in including the last billing comment and last billing comment date on a BAR.PAT report. MEDITECH...Read More

SQL Tip — Making Your Own Legos

Today we play with Lego™, or rather we make our own Lego. No, not this way, but in SQL. One of the tremendous things about SQL and the Data Repository is that you can roll your own building blocks...Read More

Writing files from NPR report to a shared folder from a scheduled or submerged report (MAGIC Only)

The only "officially supported" mechanism to write a file to a non-MEDITECH machine is to use FTP. This means you need to set up an FTP as a service on the destination machine, which typically...Read More

NPR Tip (MAGIC only) - Printing Radiology ($T RAD) Text and how to use ECB/ECE loop in a report

In October 2002, we published a tip on how to print Radiology report text in an NPR report. At the time, there was no field or utility to do this (or possibly I just liked to do things the hard way),...Read More

SQL Tip - Parsing Patient and Provider Names

Tis but patient name tis my enemy; Thou art five parts, though not in MEDITECH. What is MEDITECH? It is nor first, nor last, nor suffix, nor prefix, nor any other part belonging to a name. Oh! Be...Read More

NPR Tip (MAGIC or Client/Server) – When Standard Graphics Attributes Fall Short

One limitation of NPR report graphical attributes is that you can only start vertical lines at the top of a box. Here is an example of a page header for a discharge order form: Many preprinted forms...Read More