Commonly used SQL Scripts
This page contains the commonly used SQL scripts to retrieve Data from the CMO Data Warehouse Database. These scripts have been tested on v17 R2007 and may require minor adjustments based on the version being tested on.
- Event Register View
- SQL Script to retrieve data that is similar to the data viewable in the CMO Events Register
- Action Register View
- SQL Script to retrieve data that is similar to the data viewable in the CMO Actions Register
- Finding Register View
- SQL Script to retrieve data that is similar to the data viewable in the CMO Findings Register
- Dashboard PHAIR Widget view
- This SQL script extracts raw information required to get PHAIR widget data (which is also available as a part of CMO Application).
- Event list with non workflow Actions
- Script to show the Information for all the NON Workflow Actions linked to the Events in Data Warehouse Database
- Installing SQL CLR to translate Body Heatmap points
- This article allows for installing a SQLCLR function to translate body heatmap data to a readable format.
- Events and Actions count check
- Script to check the current count of Events and Actions in Data Warehouse. This script compares the counts across four databases - the primary CMO Transactional Database with the three additional Databases created as part of the CMO Data Warehouse architecture.
- Timezone conversion
- All date time objects in CMO is stored in UTC format. This allows a uniform and centralised datetime structure in the Database. Usually, the Application layer does the datetime conversion when presenting the Datetime in the users' browser. However, if a SQL script is required for conversion to a different timezone - this can now be achieved with SQL Server 2016+ using the "AT TIME ZONE" function. https://docs.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql?view=sql-server-ver15
- Script to get the Frequency Rates Module View
- Script to get the Frequency Rates Module View from the CMO Data Warehouse Database