Pro SQL Server 2012 Reporting Services

CHAPTER 2  REPORT AUTHORING: DESIGNING EFFICIENT QUERIES

Figure 2-3. Working with the graphical query designer in SSMS

This initial query is a relatively simple one; it uses four tables joined on relational columns. Through
the graphical query designer, you can add basic criteria and sorting, and you can select only two fields
for the report: a count of the patients and a specific medical diagnosis. You can make the Sort Type of
the count Descending so that you can see the trend for the most common diagnoses. You can directly
transport the SQL query to a report, and we’ll show you how to do that in Chapter 6. Listing 2-2 shows
the query produced. You can find the code for this query in the code download file Top10Diagnosis.sql
in the Source Code/Download area of the Apress Web site (http://www.apress.com) in the SQL Queries
folder.

Listing 2-2. The SQL Query Produced Using the Graphical Query Designer to Return the Top Ten Patient

Diagnoses

SELECT TOP 10
COUNT(DISTINCT Patient.PatID) AS [Patient Count]
, Diag.Dscr AS Diagnosis
FROM
Admissions
INNER JOIN Patient ON Admissions.PatID = Patient.PatID
INNER JOIN PatDiag ON Admissions.PatProgramID = PatDiag.PatProgramID
INNER JOIN Diag ON PatDiag.DiagTblID = Diag.DiagTblID
GROUP BY
Diag.Dscr
ORDER BY
COUNT(DISTINCT Patient.PatID) DESC

Table 2-2 shows the output of this query.