Skip to main content
Mitratech Success Center

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
    Event Register View
    SQL Script to retrieve data that is similar to the data viewable in the CMO Events Register
    • Action Register View
      Action Register View
      SQL Script to retrieve data that is similar to the data viewable in the CMO Actions Register
      • Finding Register View
        Finding Register View
        SQL Script to retrieve data that is similar to the data viewable in the CMO Findings Register
        • Dashboard PHAIR Widget view
          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
            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
              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
                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
                  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
                    Script to get the Frequency Rates Module View from the CMO Data Warehouse Database

                     

                    • Was this article helpful?