Query Generation Shortcut

Overview

With ~1079 tables, ~558 views, and ~3044 stored procedures, the Allscripts Enterprise EHR Works database can be pretty overwhelming to query for a desired data set. Given the normalization in place, it is not unlikely that you will have to join multiple tables to obtain this dataset. Certainly database diagrams are very useful in determining how tables and views are related, but in terms of query formulation, a quick shortcut is to go through the motions of creating a view, then utilize the underlying query SQL Server Management Studio provides with view construction.

Approach

1. Within SQL Server Management studio, select the database of choice, the right-click on the "Views" folder of that database. Select "New View":


2. Next, select the tables you wish to query from. In our example, the desire is to query Provider's fax number (stored in the Person_Phone table), yielding provider name as well as EntryCode, thus the Provider, Person and Person_Phone tables are selected and added:


3. Lastly, select the columns you wish to include in the query output. Once column selected has been made, copy the query and use as a base to your query construction:

Acknowledgment

Thank you to Lee Durks at ABQHP for presenting this useful tool.