Report Writing

Interoperability and EHR Optimization

Privacy and Security

TSQL Concatenation + operator vs CONCAT vs CONCAT_WS

Concatenation in TSQL is often handled using the + operator. However, using + for concatenation requires the coder to account for data types and NULLs.

Read More

T-SQL- Guaranteeing the Database Context in Dynamic SQL

It is rare that this will come up, but in certain circumstances you may need to change (or guarantee) the database in which a query executes.

This can really plague you when you’re working with multiple databases on multiple servers. You don’t want to forget which database you’re in… and execute code in the wrong place.

Read More

T-SQL- Error: “Cannot resolve the collation conflict between…”

One day you write some code on a new system, or using tables in the database you’ve never used before, and BOOM! A perfectly ordinary query blows up… what is this collation business??!

Read More

Don’t Forget lowly SourceID!

Ignore SourceID at your peril!

When creating joins in a SQL query, best performance is often attained by including as many primary key columns in the JOIN predicates as possible. In Meditech’s Data Repository, the SourceID column is often overlooked, especially by SQL newbies. SourceID is the first primary key column in virtually every table in Data Repository databases and simply adding this column to your joins can improve performance dramatically.

Read More

T-SQL- SQL Agent Owners and the Lotto

Here’s a sad scene: You’ve been creating and scheduling SQL Agent jobs for years. You’ve got database maintenance jobs set up, backups, SSIS Packages to import and export data, SQL-only jobs to build datamarts. A whole ecosystem of finely tuned, perfectly running activities … all keeping your DR in tip-top condition.

Read More

T-SQL- Dropping SQL Objects Cleanly

For today's blog post, we want to help solve this common problem: how to clean up custom objects like #Temp or ##Temp tables in a SQL database when they are no longer needed.


As we create custom objects in a SQL database like the DR, we need to be mindful of cleaning them up when they are no longer needed, or when we need to clear an old object out, before re-creating it.

Read More

SSRS: Limit the number of columns on a matrix

For today's blog post, we want to help solve this common problem: how to limit the number of columns in a matrix to prevent it from exceeding the page width, when there may be any number of columns.

The following information is based on Limit no of columns in SSRS matrix report By Challen Fu, Microsoft.

Read More

T-SQL + MEDITECH DR – Patient Location at a Point in Time

There is lots of data stored in MEDITECH data, but invariably you are going to encounter that one bit of data that you need, that is not helpfully stored in the tables you are using.

Read More

T-SQL- Reducing Log Usage with a Looped DELETE

As we go through our daily lives of reporting and analyzing data, we use SELECT constantly. It’s like breathing after a while… and while we might be keeping an eye on the performance of our queries, so that we don’t use unnecessary system resources, they generally don’t crash out on a resource constraint issue and interrupt other processes. SQL Server is good at managing them.

Read More

T-SQL- Using Linked Server Data

In a modern hospital environment, you inevitably have multiple applications holding disparate sets of data. But that data is often related to the data in other systems, and sooner or later (generally sooner) someone is going to ask you something like:

Read More