SQL + SSRS Tip: Time Blocking

Wait, what is “time blocking”?

It’s generating a list of start and end dates/times in a result set, and then using that set of rows to do something else, like:

• Generate a set of extract files for a vendor, with one set of files per time block line.
• Count visits or patients seen in each time block line, even if there are no visits in the time period.
• Look for clinical documentation that wasn’t done when it was supposed to be done.
• And so on…

We use the time block result set as our starting point, rather than attributing dates/times based on something else. Remember that you can’t always count on an event happening on a day and time.

To quickly and easily generate the kinds of sets below, here are links to the Code:

IatricTimeBlocks.sql

IatricTimeBlocksExample

Like so...

Note that since we’re using a stored procedure to get the time block, we need to create a table variable to hold the output. Then we use @tDays as the FROM table from which to start, and we LEFT JOIN to AdmVisits because we want to make sure we still have a row from @tDays even when there are no visits on that day.

However, we also want to see the SDC visit count, but not lose the original row from @tDays. So, we can’t use the WHERE clause to do LIKE ‘%SDC’ because then the days with no visits will drop. This means we move that criteria into the ON (=) clause in the LEFT JOIN.

If you haven’t used criteria inside your ON (=) besides the linking fields, this may look odd! But it works. You can use whatever criteria you want there, since a JOIN to another table is really a query to another table.

Also note that our ADV.Status field in the SELECT list needs a COALESCE() to provide a value for days where there are no visits. We also need to account for COUNT() needing to report a zero on days when there are no visits… If you use COUNT(*) you will get at least 1 in the field (because @tDays has a value even when AdmVisits does not).

Time Blocking in SQL Server Integration Services

Another handy practice is to use the time block recordset to create sets of files for a vendor on those days when the implementation team of your revenue cycle project comes around and says something like:

“You know those daily files you have scheduled for us? We need five years of historical data, with one set of files per calendar month. Can you get us that tomorrow?”

That’s either 12 x 5 = 60 manual runs of your code or one run of an SSIS package with time blocking baked in. This has happened to me more than once, so now I use a template SSIS .dtsx to start, which implements this structure for me from the get-go.

To set it up by hand:

In the Variables pane of your SSIS package…

1. Create an rsBlocks variable with global scope and type Object.
2. Create dFromDate and dThruDate as variables with global scope and type DateTime. Create cRange as a global scope String variable (in SSIS 2012 and after, these could be Parameters instead of variables).
3. Set the Values of dFromDate, dThruDate and cRange appropriately.
   
4. Also create variables for character string versions of the dates, as cFromDate and cThruDate, also of global scope and type String. These are Expressions:
   
   
5. And cTimeBlockQuery as a global String, which will call our stored procedure via an Expression:
   
   
6. Once our variables and expressions are set, we add a DataFlow task to the top of our DTSX file - "Build Time Blocks Recordset" - and inside that DataFlow task, we add two components:
   
   
7. An OLE DB Source ("Query SQL for Time Blocks") that executes the query from a variable (cTimeBlockQuery):
   
   
8. The destination for the query results is a Recordset Destination which saves the data into the rsBlocks object. This lets us define an in-memory table for use by SSIS.
9. Immediately after the Time Blocks recordset is created, our next step is a Foreach Loop Container called "For Each Time Block" which loops through rsBlocks and executes everything inside the container for each time block row.
10. To get the loop container to work, on the Collection pane, we set the AOD Source Object Variable to our rsBlocks objects.
11. We set the Enumeration Mode to Rows in the first table.
12. Then on the Variable Mappings pane we add two rows; the first maps Index 0 to our dFromDate variable, and the second maps Index 1 to our dThruDate variable:
    
    
13. Now, inside that Foreach Loop Container, we add the DataFlow tasks that generate files.
14. Remember to create each filename in a variable, controlled by an Expression, that includes a date range (or some other identifier) that is different for each dataset created. That way you don't overwrite your work.

You can also get the same kind of results by creating a Date Dimension table in [zcus] and populating it with all of the time points you’re interested in from 1753-01-01 to 9999-12-31, and then using a select against that table to get the components you want.

Extra Credit

Create a table-valued function version of the stored procedure, which is more practical in reporting code, where the sp is handier when used in SSIS.

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 our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!