DR Report Development Strategies using Transact-SQL

1. DR Report Development Strategies using Transact-SQL By Acmeware, Inc. Glen R. D’Abate - President

2. Introduction • Acmeware • Course Objectives • Review Participants Experience

3. Course Summary • T-SQL Overview • T-SQL Basics • SELECT Statement • TABLE JOIN Strategies • WHERE Filters & Using T-SQL Functions • GROUP BY & HAVING • ORDER BY & UNION • Advanced T-SQL Topics: • Sub-Queries, Temp Tables, & Embedded Queries • Parameters • Cursors • Indexes

4. Lots of Examples • Examples are derived from real problems requested by our clients • Most statements have be drastically simplified to demonstrate the topic of discussion • We will provide example code via e-mail if you have an interest in receiving these

5. Why use T-SQL? • Server side-processing • Modular Solutions Construction • T-SQL Extends ANSI SQL • Access to SQL Server Function Set • Access to SQL Server System Stored Procedure & Extended Stored Procedures

6. What is Created with T-SQL • Query Analyzer Ad-hoc Queries • SQL Server Stored Procedures • SQL Server Views • Application Specific Queries that pass-through (e.g., Access, SRS, etc.)

7. Where to use T-SQL Code • Query Analyzer • Microsoft Access • Visual Basic / C# and .ASP • Crystal Reports / Crystal Enterprise • Cognos / Actuate / Business Objects

8. T-SQL Basics • SELECT, DISTINCT, Aggregate Functions • FROM, JOIN, ON • WHERE • GROUP BY • HAVING • ORDER BY • UNION

9. SELECT / DISTINCT • The SELECT Statement identifies fields or columns of data that will be returned in the Result Set • Column values may be • An explicit value from a data source • An aggregate of a data value • A derived value from a T-SQL expression, • A literal value

10. Result Set • A set of Records (or Rows) returned as a result of processing the T-SQL code. Each and every record has a value or a NULL for each column or field defined in the T-SQL statement.

11. Example Result Set Result Columns Result Records NULL values: NULL implies “unknown”

12. SELECT Statement About as simple as it gets. Asterisk means return all columns

13. SELECT with Aggregate Counts all BillingIDs in table Total all ChargeTotals in table Totals charge Amounts for a given SourceID & VisitID

14. SELECT with Expression Expression with string manipulation function IsNull() function is used often with DR data Note missing column name

15. SELECT with Literal Literal expressions are common when the UNION operator is used as a method to indicate which result set of the UNION a record was derived from. Note literal expressions are omitted from the Group By statements

16. SELECT Distinct Distinct means distinct record in the Recordset, not distinct column unless the Recordset has only one column.

17. SELECT Statement - TOP N TOP N limits the result set to the first N records. This is used most often with the ORDER BY clause, or when trying to get a quick snapshot of data.

18. ANSI SQL Syntax • SELECT, DISTINCT, Aggregate Functions • FROM, JOIN, ON • WHERE • GROUP BY • HAVING • ORDER BY • UNION

19. DR Table Joining Strategies • Primary Keys are defined in all tables and table data is sorted by the MEDITECH defined Primary Key Clustered Index • The SourceID is a DR-only construct defined to allow multi-database MT application implementations to exist in the same DR table. Also, multiple MT universe implementation can feed a single DR using SourceID to delineate data. SourceID is in virtually all DR tables. • Foreign Keys are only identified by MEDITECH DR naming convention (i.e., end in ID). No declared Foreign Key relationships exist) • The hierarchy that existed in the NPR data structures are typically reconstructed through PK to PK table links within a module

20. DR Table Joining Issues • PatientID defines Patient level data (i.e., Medical Record Unit Number externally) • VisitID defines Visit level data (i.e., AccountNumber) • VisitID joins provides cross modules linkage and should be used for inter-module table joins • Most modules have internal IDs for linking within (e.g., ABS - AbstractID; B/AR - BillingID; LAB - SpecimenID) which should be used for intra-module table linking

21. More DR Joining Factors • SourceID should typically be included in DR Table Joins • Outer Joins are common where Nulls may exist in a Table (e.g.., looking for “Attending Provider” if he/she exists.) • Unit Number or PatientID Joins can be used to compare a patient’s visits with other visits by the same patient (i.e., revisits within 72 hours)

22. Five Common DR Join • Intra-Module Join • Module - Dictionary Join • Inter-Module Join • Table Self-Join • Outer Join (Left or Right)

23. Intra Module Join Example: Create a JOIN that includes general Billing / Accounts Receivable (B/AR) information (i.e., from NPR main segment) and includes the Patient demographic as well as financial information associated with a visit.

24. B/AR Intra-Module Join BarVisitFinancialData is at the same segment level of the NPR structural as BarVisits (I.e., both are BAR.PAT Always use SourceID within a module Note table naming nomenclature. Main segment tables are plural (e.g., BarVisits). Subordinate NPR tables have the same prefix with the addition of the Segment name (e.g., BarVisitFinancialData, BarVisitProviders)

25. Module Join to a Dictionary Example: Create a JOIN that includes B/AR Charge detail information as well as detail about the specific charge Transactions. This requires the use of the MEDITECH B/AR Procedure dictionary, which is named ‘DBarProcedures’ in the DR.

26. B/AR Module to B/AR Dictionary Join Often times, the fact table FK will be named more specific than the corresponding PK dictionary table (e.g., TransactionProcedureID, OrderingProviderID, etc.) Dictionaries in the DR all begin with ‘D’module (e.g., DMisProviders, DBarProcedures, DLabTests, etc.)

27. B/AR Module to MIS Dictionary Join When linking to MIS dictionaries, the SourceID may, or may not be appropriate. This depends on whether you are using a multi-database module (e.g., B/AR) and multiple databases exist. Also, we have seen the LAB module set up with different SourceIDs

28. Inter Module Join Example: Create a JOIN that includes Visit Demographic information with Laboratory Test results.

29. Inter-Module with Lab data SourceID should be used if it can be used VisitID is key for cross module joins

30. Inter-Module with MRI Demo-Recall data Patient ID is used for MR queries.

31. Table Self Join Example: This type of query can be used to look at activity within a visit in the AdmVisitEvents table. Create a JOIN that finds patient transferred from a location, to a second location, then back to the original location within within 4 hours of being transferred out of that unit.

32. Self Join using Visit Events This will identify the same transfer multiple times if multiple transfers occur within a 4 hour window

33. Outer Join Example: Create a JOIN that identifies a patient’s top two ICD-9 procedures and top two diagnosis, regardless of whether or not they exist

34. Outer Join with ICD-9s & Diagnoses Alias the ICD-9 and Diagnosis tables twice in order to identify primary and secondary occurrences, if they exist

35. Outer Join (Example 2) Example: Create a JOIN that identifies all visits (by discharge or service date) that have occurred more than two months ago, and do not have a Bill generated

36. Outer Join Finding Missing Bills Filter for missing BarBills entries

37. ANSI SQL Syntax • SELECT, DISTINCT, Aggregate Functions • FROM, JOIN, ON • WHERE • GROUP BY • HAVING • ORDER BY • UNION

38. The WHERE Clause • Specifies a filter condition to restrict the rows returned to the record set • The WHERE expression simply evaluates a Boolean (True or False) value that determines if the record, as identified in the JOIN process) is to be returned • May use operators, functions, and sub-queries in the expression

39. WHERE Clause: Find all current inpatients and list the patient’s admission date, current location – defined as room location, room, and bed Patients who have been admitted, but not yet discharged are considered current inpatients

40. WHERE Clause: Identify the Reason employees have been terminated in less than one year for specific job codes This example uses the function DateDiff() as well as the IN function.

41. WHERE Clause: Find patients with first name beginning with ‘J’, specific Zip Codes and Diagnoses Like function allows for pattern matching in the WHERE filter IsNumeric() function can be used to verify a value i numeric before performing a mathematical function…this is VERY useful when working with DR data

42. WHERE Clause: String manipulation functions to clean up Name conversion problem Left(), Right(), and SubString() functions allow for string extraction from within a string Identifying a strings within a string can be performed using the Charindex() function

43. ANSI SQL Syntax • SELECT, DISTINCT, Aggregate Functions • FROM, JOIN, ON • WHERE • GROUP BY • HAVING • ORDER BY • UNION

44. Aggregate Functions • T-SQL Aggregate functions are used to summarized data values by: • Count(), Count(Distinct) • Avg(), Avg(Distinct) • Sum(), Sum (Distinct) • Max() & Min() • HAVING Statement allows filtering of aggregate values

45. Aggregate Example: Census by Location and Date The Count() aggregate counts all non-NULL entries. If Nulls are encountered, a warning is displayed. The AdmNursingCensus table captures the census at midnight run. Each inpatient is assigned to the current location in which they reside.

46. Aggregate Example: Create a T-SQL statement that identifies by Final DRG the Average Length-of-Stay (as defined by Medicare), and number of Medications a patient received. Only consider patient visits in 2004 and only include DRG results if more than 2 visits occurred with the DRG.

47. Medicare defines LOS as 1 for a single days stay, and the number of midnights for a multiple day stay The statement counts the unique DrugIDs that were given at anytime during a stay. The HAVING filter removes records from the result set AFTER the aggregation has been performed. We only consider DRS when more than 2 visits with the DRG have occurred.

48. Aggregate Example: Identify the Average Charges associated with a patient visit by Attending Provider for a given Major Diagnosis Group and also show the number of cases that were used to compute this average?

49. Diagnosis codes are sometimes entered with a leading character. In this case, remove the leading character. Because a single visit may have multiple Diagnoses, we must count DISTINCT VisitIDs Charge Total is available from BarVisitFinancialData. It could also be derived from BarChargeTransactions Each visit should only have one ‘Attending’ Provider. You may need to verify this. Only Final Bill (FB) accounts are considered and only those with a diagnosis that contains a “.” GROUP BY statement must match the non-Aggregate columns in the SELECT statement

50. ANSI SQL Syntax • SELECT, DISTINCT, Aggregate Functions • FROM, JOIN, ON • WHERE • GROUP BY • HAVING • ORDER BY • UNION