--- title: "Building your own SQL queries from scratch" slug: "building-your-own-sql-queries-from-scratch" updated: 2026-11-11T17:45:34Z published: 2026-11-11T17:45:34Z canonical: "docs.quantivly.com/building-your-own-sql-queries-from-scratch" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.quantivly.com/llms.txt > Use this file to discover all available pages before exploring further. # Building your own SQL queries from scratch ## Before you start - Check out our [SQL tutorial](/v1/docs/welcome-to-the-quantivly-sql-tutorial) - Read our [In-Time Metrics](/v1/docs/in-time-metrics) article where you’ll find useful resources to easily get time-based metrics from pre-built queries ## Understanding the database schema You can also build your own queries from scratch. For that, it's important to understand the underlying database schema. To effectively run SQL queries on the Quantivly data layer, it's essential to familiarize yourself with the underlying database schema. The diagram below provides a blueprint of how the data is structured, how tables are organized, and how they relate to each other. Note that the diagram is quite detailed, and the font may appear small. You can always zoom in to see the elements more clearly. In addition, to make the diagram easier to understand, we've designed it with: - **Functional Organization:** Tables are spatially arranged and annotated based on their functional roles, making it easier to locate the data relevant to your needs. - **Table Relationships:** The schema illustrates relationships between tables, such as primary keys and foreign keys, which are crucial for joining tables and retrieving comprehensive data sets. - **Annotations and Descriptions:** Each table and column includes annotations that provide additional context, such as data types, default values, and constraints. > **TIPS:** Understanding these schemas will help you construct your queries. We encourage you to refer to this diagram as you work through the tutorial and develop your own queries. ### **Primary schema** The schema in `public` is the **primary schema** containing harmonized and structured concepts derived from both the Radiology Information System (RIS) and DICOM data. It contains both native concepts from the data sources and new concepts not native in the original data, but extrapolated from it. For example, this schema exposes information about: - **The imaging provider environment**, including each facility with their own timezone, their imaging equipment, staff, etc. - **Appointments**, harmonized and structured from RIS HL7 data, including scheduling orders, clinical orders and results - **Examinations and acquisitions**, new concepts derived from DICOM studies and series that are essential from an operational perspective - **How appointments and examinations are linked**, critical to evaluate metrics like *delay*, Slot utilization *ratio*, etc. - **Annotations**, with a powerful system of *recurring rules* (*e.g.*, able to annotate a *"blocked time reserved for research every 3rd thursday of the month"*) ![](https://cdn.us.document360.io/d2a031b3-e484-4983-9d46-1633ed0c8eef/Images/Documentation/image(55).png) ### **DICOM schema** We have developed a powerful DICOM harmonization engine able to *pivot* the "Entity-Attribute-Value" (EAV) model of DICOM into a **highly optimized, relational database**. The result is in the `dicom` schema, specially designed to capture all the detailed, highly granular information in DICOM studies and series, including: - DICOM study and series properties - Series' modality-specific technical parameters - Structured reports and their tree-like structure embedded into a relational database Our schema is built around these core concepts: - **Naming conventions:** Fields that closely match their DICOM counterparts have a `dcm_` prefix, while fields without the `dcm_` prefix represent new concepts added to our ontology. This naming convention helps differentiate original DICOM fields from the new concepts we've introduced, making the schema more intuitive to navigate. - **Concept of collection of slices:** DICOM is fundamentally a **slice-centric** data model. For example, while many might refer to "the echo-time (TE) of a series" in MR, this is not possible and too limiting, *e.g.* as multi-echo sequences lead to multiple TE values within the same series. Similarly, many DICOM attributes can vary within a single series—we've even encountered different spatial resolutions in the same series! To address this, and "pivot" the EAV model of DICOM into a relational database, we've introduced the concept of **collections of slices**, such as 3D volumes for MRI (`mr_volume`) and CT (`ct_volume`), each associated with their acquisition parameters (e.g., TE, TR, resolution, b-value, etc.). Implementing this required decoding proprietary information from each vendor (*e.g.*, SIEMENS MOSAIC, Philips Enhanced DICOM, GE DICOM, Siemens XA DICOM, etc.), but **now you can simply query your data using these higher-level concepts!** - **Concept of aliases:** You probably know it: certain DICOM attributes tend to change names over time, *e.g.*, the scanners' `StationName`, the `InstitutionName`, `InstitutionAddress`, etc. Even `ManufacturerModelName` or `Manufacturer` sometimes change during scanner upgrades. As a result, a same DICOM equipment (or institution) can have different *DICOM identities*. Our ontology includes a mechanism of *aliases* that allows us to group these various identities under a **single, consistent identity** (e.g., many `dicom.equipment_alias` for one `dicom.equipment`). You won't have to alias things yourself; we've developed rules and alerts to detect these changes early and handle the aliasing for you. - **Study aliases:** While you may not need to know this in detail, it's still interesting to understand. Each DICOM series repeats the information of the study it relates to, but this information can be inconsistent across series (DICOM is beautiful 😉 ). That's why, for every DICOM series, we maintain the version of the study as described in that series—this is what the `study_alias` represents. Meanwhile, in the `study` table, you have access to the harmonized version across all study aliases, typically determined using a majority voting mechanism to establish the "best guess" value at the study level. ## **Build your first queries from scratch** We'll cover the basic concepts and provide examples to help you understand how to retrieve the information you need. ### **Inspect the most recent 100 examinations** The Quantivly harmonization engine extracts the concepts of examinations and acquisitions from DICOM studies and series. Specifically, an examination is defined as a contiguous collection of acquisitions for the same patient on the same equipment. Start by examining the central table called **examination**. Here's a basic query to retrieve the **examination_id** (primary key)and the corresponding **examination_datetime** for the 100 most recent examinations: ```sql select    examination_id,    examination_datetime from examination order by examination_datetime desc limit 100; ``` ```sql examination_id |  examination_datetime ----------------+------------------------         100006 | 2024-01-15 17:45:12+00         100005 | 2024-01-15 17:02:40+00         100004 | 2024-01-15 16:25:38+00         100003 | 2024-01-15 15:01:39+00         100002 | 2024-01-15 14:46:04+00         100001 | 2024-01-15 13:25:50+00 ... ``` ### **Add more columns** You can start to add more columns to inspect properties of those examinations: ```sql select    examination_id,    examination_datetime,    accession_numbers,    study_descriptions from examination order by examination_datetime desc limit 100; ``` ```sql examination_id |  examination_datetime  | accession_numbers |           study_descriptions ----------------+------------------------+-------------------+---------------------------------------         100006 | 2024-01-15 17:45:12+00 | {1000006}         | {"MR SPINE ENTIRE W AND WO CONTRAST"}         100005 | 2024-01-15 17:02:40+00 | {1000005}         | {"CT HEAD WO IV CONTRAST"}         100004 | 2024-01-15 16:25:38+00 | {1000004}         | {"CT HEAD WO IV CONTRAST"}         100003 | 2024-01-15 15:01:39+00 | {1000003}         | {"MR BRAIN W AND WO CONTRAST"}         100002 | 2024-01-15 14:46:04+00 | {1000002}         | {"CT ABDOMEN PELVIS W CONTRAST"}         100001 | 2024-01-15 13:25:50+00 | {1000001}         | {"MR LUMBAR SPINE WO CONTRAST"} ... ``` Above you can see the accession numbers and study descriptions for each examination. Note that these are **arrays**, as you may have different accession numbers and/or study descriptions within an examination (*e.g.*, think of a “MR Brain w/wo Contrast + MR Spine w/wo Contrast” combo exam in which acquisitions pre- and post- contrast are intertwined). ### **Count the number of exams** Often, you'll want to count the number of examinations. You can achieve this using the `count()` function. For example, the number of exams in 2024: ```sql select    count(distinct examination_id) as exam_count from examination where extract(year from examination_datetime) = 2024; ``` ```sql exam_count ------------       3450 ``` **Important Reminder:** Be aware that joining tables may result in duplicate rows. For example, if you were to join `examination` with the `acquisition` table (more on that later), the number of rows would be multiplied. To ensure accuracy, especially when counting entries, use `distinct` inside of a count function to obtain unique counts of examinations. This method helps maintain the integrity of your data analysis by avoiding inflated results due to duplicate records. Adding `distinct` to the count function below makes it return the correct count, 1. Without it, the query would return 8, one row for each of the examination's 8 acquisitions. ```sql select    count(distinct examination_id) from examination inner join acquisition using (examination_id) where '1000003' = any(accession_numbers) ``` ### **Adding imaging equipment information** #### **Imaging resources** The primary way to identify information about imaging equipment is via the `imaging_resource` and `resource` tables in the main schema. For example, to list all available *imaging resources* and their name: ```sql select    resource_id,    name from resource inner join imaging_resource using (resource_id); ``` ```sql resource_id |    name -------------+-----------           1 | SCANNER-A           2 | SCANNER-B           3 | SCANNER-C           4 | SCANNER-D           5 | SCANNER-E           6 | SCANNER-F           7 | SCANNER-G (7 rows) ``` > **Note:** while `resource` and `imaging_resource` have their own primary key (`resource_id` and `imaging_resource_id`, respectively), we have in the database a constraint enforcing `resource_id = imaging_resource_id`. Knowing that can be convenient to avoid one `join` in complex queries. Imaging resources in our ontology corresponds to a **high level, functional concept** - you can think of it as an *MR Room* for an MR device, for example (but not the device itself). The specific DICOM equipment (see below) of an `imaging_resource` is linked via the `resource_to_dicom_equipment` associative table. Here is an example query counting the number of examinations for each imaging resource: ```sql select    resource.name,    count(distinct examination_id) as exam_count from examination inner join resource_to_dicom_equipment using (dicom_equipment_id) inner join resource using (resource_id) where extract(year from examination_datetime) = 2024 group by resource.name; ``` ```sql    name   | exam_count -----------+------------ SCANNER-A |       2154 SCANNER-B |       1997 SCANNER-C |       2715 SCANNER-D |       4123 SCANNER-E |       3570 SCANNER-F |       1758 SCANNER-G |       3508 (7 rows) ``` **Important:** Although an `imaging_resource` is most often linked to a *single* `dicom.equipment`, it can be associated with multiple pieces of equipment. This occurs when the device of an `imaging_resource` is replaced—e.g., when an old 1.5T scanner is replaced with a newer 3T scanner in the same room. These two devices have different "DICOM identities" but can be associated with the same `imaging_resource`. By focusing on the **high-level, functional concept** of an imaging resource, the `imaging_resource` allows you to accurately and continuously evaluate metrics (e.g., examination counts) over time, even when equipment changes. This data structure also enables you to compare metrics *before* and *after* equipment replacements if needed. #### **DICOM equipment** The `dicom.equipment` table contains the specific *DICOM equipment identities*, with their serial numbers, station names, and more. The query below shows DICOM characteristics of imaging resources: ```sql select    resource.name,    equipment.manufacturer,    equipment.manufacturer_model_name,    equipment.station_name,    equipment.device_serial_number from resource inner join imaging_resource using (resource_id) inner join resource_to_dicom_equipment on resource_to_dicom_equipment.resource_id=imaging_resource.imaging_resource_id inner join dicom.equipment on resource_to_dicom_equipment.dicom_equipment_id=equipment.equipment_id; ``` ```sql    name   | manufacturer | manufacturer_model_name | station_name | device_serial_number -----------+--------------+-------------------------+--------------+---------------------- SCANNER-A | SIEMENS      | Prisma                  | STATION-A    | XXXXX SCANNER-B | SIEMENS      | Skyra                   | STATION-B    | XXXXX SCANNER-C | SIEMENS      | MAGNETOM Sola           | STATION-C    | XXXXX SCANNER-D | SIEMENS      | MAGNETOM Vida           | STATION-D    | XXXXX SCANNER-E | SIEMENS      | MAGNETOM Vida           | STATION-E    | XXXXX SCANNER-F | SIEMENS      | TrioTim                 | STATION-F    | XXXXX SCANNER-G | SIEMENS      | Verio                   | STATION-G    | XXXXX (7 rows) ``` #### **DICOM equipment aliases** As you know, it is not uncommon for station names to change, leading potentially to multiple DICOM identities for the same device. If you want to list the various `StationName` of the aliases for each `equipment`, you can use the `equipment_alias` table. For example: ```sql select    resource.name,    equipment.manufacturer,    equipment.manufacturer_model_name,    equipment.station_name,    array_agg(distinct equipment_alias.dcm_station_name) from resource inner join imaging_resource using (resource_id) inner join resource_to_dicom_equipment on resource_to_dicom_equipment.resource_id=imaging_resource.imaging_resource_id inner join dicom.equipment on resource_to_dicom_equipment.dicom_equipment_id=equipment.equipment_id inner join dicom.equipment_alias using (equipment_id) group by 1,2,3,4 ``` ```plaintext    name   | manufacturer | manufacturer_model_name | station_name |           array_agg -----------+--------------+-------------------------+--------------+------------------------------- SCANNER-A | SIEMENS      | Prisma                  | STATION-A    | {"",STATION-A-OLD,STATION-A2} SCANNER-B | SIEMENS      | Skyra                   | STATION-B    | {"",STATION-B-OLD,STATION-B2} SCANNER-C | SIEMENS      | MAGNETOM Sola           | STATION-C    | {MRI,MRI_3,MRI3} SCANNER-D | SIEMENS      | MAGNETOM Vida           | STATION-D    | {MRI,MRI_4,MRI4} SCANNER-E | SIEMENS      | MAGNETOM Vida           | STATION-E    | {"",STATION-E-OLD,MRI_5} SCANNER-F | SIEMENS      | TrioTim                 | STATION-F    | {"",STATION-F-OLD} SCANNER-G | SIEMENS      | Verio                   | STATION-G    | {"",STATION-G-OLD,STATION-G2} (7 rows) ``` ### **Adding study and/or protocol information** #### **Study information** It is important to realize that a same `examination` may have multiple `StudyDescription` values. This is the case, for example, in combo exams, that combine series from different studies, *e.g.* `MR Brain w/wo` and `MR Spine w/wo`. As you saw in the second query of this tutorial, the `examination` table includes the `study_descriptions` **array** column that represents all the study descriptions found across the DICOM series of the examination, **ordered by highest frequency of occurrence**. You can explore this in your data with the following query: ```sql select    accession_numbers,    study_descriptions from examination where array_length(study_descriptions,1) >= 2 limit 100; ``` ```sql accession_numbers |                            study_descriptions -------------------+-------------------------------------------------------------------------- {1000011,1000012} | {"MR-Brain w/o Contrast","MR-Spine Entire w/ + w/o Contrast"} {1000013,1000014} | {"MR-Hip w/o Contrast Left","MR-Hip w/ + w/o Contrast Right"} {1000015,1000016} | {"CT ABDOMEN PELVIS W AND WO CONTRAST","PET/CT LIMITED W DIAGNOSTIC CT"} {1000017,1000018} | {"NM-PET/CT Torso w/ Diagnostic CT","NM-CT Neck/Chest w/ Contrast"} {1000019,1000020} | {"MR ABDOMEN W AND WO CONTRAST","MR BRAIN WO CONTRAST"} ... ``` #### **Limitations of** `StudyDescription` The `StudyDescription` typically identifies the general type of study, **but not** the specific *imaging protocol* performed. For example, you might see entries like `MR Brain w/wo Contrast`, which indicate the general area and contrast usage but do not specify whether it's for a `Stroke Protocol`, `Tumor Protocol`, or `TBI Protocol`. This lack of specificity limits the ability to distinguish between different imaging protocols within the same general study type, and limit the statistical analysis (*e.g.*, the Exam duration of `MR Brain w/wo Contrast` often ranges from 15min to 120min). #### **Concept of protocol** In addition to `StudyDescription`, the Quantivly ontology introduces the new concept of **protocol**, accessible via the `examination_to_protocol` and `protocol` tables. > **Important**: Depending on your local setup, you might have different *"protocol naming strategies"* available in your database, including: > > - Rule-based strategies from StudyDescription > - RIS-based naming strategies **if** you are able to send us protocol information via HL7 messages > - AI-based naming strategy identifying imaging protocols from the acquisition parameters (in R&D) > > When joining `examination_to_protocol`, you always need to remember to **select a specific naming strategy** in the `WHERE` clause to avoid multiplying the rows and getting incorrect counts. Here's how to list the available naming strategies (or "protocol algorithms"): ```sql select distinct    protocol_algo_id from protocol ``` ```sql               protocol_algo_id ----------------------------------------------- quantivly.protocol.rule-based.no-laterality quantivly.protocol.rule-based.keep-laterality (2 rows) ``` ##### **Rule-based naming strategy** The two default *naming strategies* available are: - `quantivly.protocol.rule-based.keep-laterality` - `quantivly.protocol.rule-based.no-laterality` Disclaimer: these strategies are based on the `StudyDescription` field and therefore are not truly represent *imaging protocols*. However, they can still be quite useful for certain purposes: - **Handling Laterality:** The `no-laterality` strategy removes laterality information from study descriptions. This can be helpful when you want to combine studies that are essentially the same except for laterality. For example, `MR Knee Left` and `MR Knee Right` would both be categorized as `MR Knee`. - **Handling Combo-Exams:** For examinations that include multiple studies (combo-exams), all unique `StudyDescription` values within an examination are concatenated into a single string, ordered alphabetically. This approach helps in grouping and analyzing combo-exams as unified categories. You can query these naming strategy with: ```sql select    resource.name,    accession_numbers,    protocol.protocol_name from examination inner join resource_to_dicom_equipment using (dicom_equipment_id) inner join resource using (resource_id) inner join examination_to_protocol using (examination_id) inner join protocol using (protocol_id) where extract(year from examination_datetime) = 2024    and protocol_algo_id = 'quantivly.protocol.rule-based.keep-laterality'; ``` ```sql    name   | accession_numbers |              protocol_name -----------+-------------------+------------------------------------------ SCANNER-A | {1000021}         | MR-Knee w/o Contrast Left SCANNER-B | {1000022}         | MR-Brain w/o Contrast SCANNER-D | {1000023}         | MR-Arthrogram Upper Extremity Right SCANNER-A | {1000024}         | MR-Craniospinal SCANNER-B | {1000025}         | MR-Upper Extremity Entire w+w/o Cont Lft ... ``` The same with `quantivly.protocol.rule-based.no-laterality`: ```sql    name   | accession_numbers |            protocol_name -----------+-------------------+-------------------------------------- SCANNER-A | {1000021}         | MR-Knee w/o Contrast SCANNER-B | {1000022}         | MR-Brain w/o Contrast SCANNER-D | {1000023}         | MR-Arthrogram Upper Extremity SCANNER-A | {1000024}         | MR-Craniospinal SCANNER-B | {1000025}         | MR-Upper Extremity Entire w+w/o Cont ... ``` **Always remember:** if you join the `examination_to_protocol` and `protocol` tables but forget to filter with `WHERE protocol_algo_id = '...'`, then each row will display multiple times, once for each naming strategy. For example, with two naming strategies: ```sql select    resource.name,    accession_numbers,    protocol.protocol_name from examination inner join resource_to_dicom_equipment using (dicom_equipment_id) inner join resource using (resource_id) inner join examination_to_protocol using (examination_id) inner join protocol using (protocol_id) where extract(year from examination_datetime) = 2024 ``` ```sql    name   | accession_numbers |            protocol_name -----------+-------------------+-------------------------------------- SCANNER-A | {1000021}         | MR-Knee w/o Contrast SCANNER-A | {1000021}         | MR-Knee w/o Contrast SCANNER-B | {1000022}         | MR-Brain w/o Contrast SCANNER-B | {1000022}         | MR-Brain w/o Contrast SCANNER-D | {1000023}         | MR-Arthrogram Upper Extremity SCANNER-D | {1000023}         | MR-Arthrogram Upper Extremity SCANNER-A | {1000024}         | MR-Craniospinal SCANNER-A | {1000024}         | MR-Craniospinal SCANNER-B | {1000025}         | MR-Upper Extremity Entire w+w/o Cont SCANNER-B | {1000025}         | MR-Upper Extremity Entire w+w/o Cont ... ``` To see how these rule-based *naming strategies* concatenate `StudyDescription` for combo-exams: ```sql select    array_length(accession_numbers,1) as n_acc_num,    protocol.protocol_name from examination inner join examination_to_protocol using (examination_id) inner join protocol using (protocol_id) where extract(year from examination_datetime) = 2024    and protocol_algo_id = 'quantivly.protocol.rule-based.keep-laterality'    and array_length(accession_numbers,1) >= 2; ``` ```sql  n_acc_num | protocol_name -----------+--------------------------------------------------------------------------------------          2 | MR-Brain w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast          2 | MR-Enterography w/ + w/o Contrast+MR-Pelvis w/ + w/o Contrast Body          2 | CT-Angio Chest w/ Contrast Post Process+CT-Thoracic Spine w/ Contrast          3 | MR-Leg (Lower) w + w/o Contrast Left+MR-Pelvis w/ + w/o Contrast MSK+MR-Spine Entire          2 | MR-Chest w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast          2 | MR-Chest w/ + w/o Contrast+MR-Orbits/Face/Neck w/ + w/o Contrast          3 | CT-Chest w/o contrast+CT-Head w/o Contrast+CT-Soft Tissue Neck w/o Contrast        ... ``` > **Note:** We found that the number of `AccessionNumber` may not necessarily correctly match the number of different `StudyDescription`. Depending on the workflow, technologists may not enter a different `AccessionNumber` for a different part of a combo exam. However, the `StudyDescription` typically populated from the scanner template may still change. ##### **RIS-based naming strategy** The previous rule-based naming strategies do not solve the unability to distinguish between different **imaging protocols** within the same general study type. If you are able to send us protocol information in your HL7 messages, you may be able to retrieve those names using the `quantivly.protocol.rule-based.source-ris` naming strategy. Contact your Quantivly team to confirm which naming strategies are available on your site. ##### **AI-based naming strategy** We are also working on an AI-based naming strategy identifying imaging protocols from the acquisition parameters. The corresponding naming strategy is `quantivly.protocol.graph-based.no-laterality`. This is still in R&D; contact your Quantivly team if you want to test it. ### **Accessing the acquisition level** The Quantivly harmonization engine identifies which DICOM series are actual acquisitions (*i.e.*, took time to acquire data) among all the DICOM series available. You can access the acquisition level information by joining the `acquisition` table. For example the query below retrieve the start and duration of each acquisition: ```sql select    accession_numbers,    study_descriptions,    acquisition_datetime,    acquisition_duration from examination inner join acquisition using (examination_id) order by examination_datetime desc, acquisition_datetime asc limit 100; ``` ```sql accession_numbers |       study_descriptions       |  acquisition_datetime  | acquisition_duration -------------------+--------------------------------+------------------------+---------------------- {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:01:39+00 | 00:00:14 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:02:35+00 | 00:02:38 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:05:48+00 | 00:01:48 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:07:51+00 | 00:02:08 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:10:14+00 | 00:02:44 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:13:03+00 | 00:02:02 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:18:14+00 | 00:06:24 {1000003}         | {"MR BRAIN W AND WO CONTRAST"} | 2024-01-15 15:24:51+00 | 00:02:08 ... ``` ### **Accessing the DICOM series of acquisitions** Each acquisition is linked to a DICOM series `dicom.series`. For example, you can retrieve the `SeriesNumber` and `SeriesDescription` with: ```sql select    accession_numbers as accession_num,    acquisition.acquisition_datetime,    acquisition.acquisition_duration,    dicom.series.dcm_series_number as series_num,    dicom.series.dcm_series_description as series_description from examination inner join acquisition using (examination_id) inner join dicom.series using (series_id) order by examination_datetime desc, acquisition_datetime asc limit 100; ``` ```sql accession_num |  acquisition_datetime  | acquisition_duration | series_num |       series_description ---------------+------------------------+----------------------+------------+------------------------------- {1000003}     | 2024-01-15 15:01:39+00 | 00:00:14             |          2 | BRN_LOC_AAHScout {1000003}     | 2024-01-15 15:02:35+00 | 00:02:38             |          6 | SAG_T1 MPRAGE (fast) (brain) {1000003}     | 2024-01-15 15:05:48+00 | 00:01:48             |          9 | AX_T2 drb (fast) (brain) {1000003}     | 2024-01-15 15:07:51+00 | 00:02:08             |         10 | AX_T2 FLAIR (fast) (brain) {1000003}     | 2024-01-15 15:10:14+00 | 00:02:44             |         11 | AX_DTI (fast) (brain) {1000003}     | 2024-01-15 15:13:03+00 | 00:02:02             |         18 | AX_SWIwave (fast) (brain)_Mag {1000003}     | 2024-01-15 15:18:14+00 | 00:06:24             |         22 | SAG_T1 SPACE_+C (brain) {1000003}     | 2024-01-15 15:24:51+00 | 00:02:08             |         25 | AX_T2 FLAIR (fast) (brain) +C ... ``` If you want to list all `SeriesDescription` of examinations, you can use the following aggregation: ```sql select distinct  protocol.protocol_name,  array_agg(    concat(series.dcm_series_number, '.', series.dcm_series_description)    order by acquisition.acquisition_datetime asc  ) as all_series from examination inner join examination_to_protocol using (examination_id) inner join protocol using (protocol_id) inner join acquisition using (examination_id) inner join dicom.series using (series_id) where   protocol_algo_id='quantivly.protocol.rule-based.no-laterality' group by 1 having count(distinct acquisition_id)<=5    -- With less than 5 acquisitions, for example limit 50 ``` ```sql            protocol_name     |                                     all_series ------------------------------+------------------------------------------------------------------------------------------- MR-Brain w/o Contrast        | {2.BRN_LOC_AAHScout,"6.AX_DTI (fast) (brain)","14.AX_T2_dl (fast) (brain)",17.SWI_Images} MR-Brain/Vent Check          | {"1.STEALTH_LOC_NO ANGLE","2.AX_T2 HASTE","3.SAG_T2 HASTE","4.COR_T2 HASTE"} MR-Brain/Vent Check          | {1.BRN_LOC_AAHScout,"5.AX_T2 HASTE","6.SAG_T2 HASTE","7.COR_T2 HASTE"} MR-Pelvis w/o Contrast MSK   | {1.PELVIS_LOC_AAHScout,9.COR_T1_dl,10.COR_T2_FS_dl,11.AX_T2_FS_dl,12.SAG_FSEIR} MR-Brain/Vent Check          | {"1.STEALTH_LOC_NO ANGLE","2.AX_T2 HASTE","3.SAG_T2 HASTE","4.COR_T2 HASTE"} MR-Cardiac (in Cardiology)   | {101.SURVEY,"201.VCG SSFP SURVEY","301.AXIAL SSFP 20 PHASES"} MR-Brain w/o Contrast        | {1.BRN_LOC_AAHScout,5.BRN_LOC_AAHScout,"9.SAG_T1 MPRAGE (fast)"} ... ``` ### **Accessing the technical parameters of a DICOM series** To access the parameters, you need to join `dicom.series` with the corresponding **modality-specific tables**, e.g. `dicom.mr_series` for MR. Then parameters for each MR volume are accessible by joining with `dicom.mr_volume`. For example, to get the values of `EchoTime` in MR: ```sql select distinct  examination.accession_numbers,  protocol.protocol_name,  series.dcm_series_number,  series.dcm_series_description,  array_agg(dcm_echo_time) as echo_times from examination inner join examination_to_protocol using (examination_id) inner join protocol using (protocol_id) inner join acquisition using (examination_id) inner join dicom.series using (series_id) inner join dicom.mr_series using (series_id) inner join dicom.mr_volume using (mr_series_id) where  '1000031' = any(accession_numbers)  and protocol_algo_id='quantivly.protocol.rule-based.no-laterality' group by 1,2,3,4 ``` ```sql accession_numbers |    protocol_name    | dcm_series_number | dcm_series_description |    echo_times -------------------+---------------------+-------------------+------------------------+------------------ {1000031}         | MR FOOT WO CONTRAST |                 1 | 3_PL_LOC               | {3.69,3.69,3.69} {1000031}         | MR FOOT WO CONTRAST |                 3 | AX_T2_FS               | {74} {1000031}         | MR FOOT WO CONTRAST |                 4 | SAG_T2_FS              | {52} {1000031}         | MR FOOT WO CONTRAST |                 5 | COR_T1_(FOOTPRINT)     | {33} ``` > **NOTE:** Above the localizer has 3 echo-times because our harmonization engine not only extrapolates the concept of MR volumes; it also extrapolates the concept of **volume stacks**. The localizer's 3 different planes have different slice orientations (COR, SAG, AX) which led to three volume stacks and, in turn, 3 values of `EchoTime`. You can also use `array_agg(distinct dcm_echo_time)` to only return distinct values. ### Classic: Filtering exams with a particular series description This query returns patient identifiers (MRN and date of birth). Handle the results according to your organization's policies for protected health information (PHI). ```sql -- First filter exams containing some series description with exams_with_series as (  select    DISTINCT examination.examination_id  from dicom.series  inner join acquisition using (series_id)  inner join examination using (examination_id)  where series.dcm_series_description ilike '%bonemri%'  -- << You can change here the filter    and examination_datetime between      (now() - '48 month'::interval) and now()           -- << You can change here the date interval ) -- Then returns various info about those exams select  examination.examination_datetime at time zone 'America/New_York' as study_datetime,  patient.medical_record_number as "patient_mrn",  patient.birthdate as "patient_dob",  resource.name as scanner_name,  equipment.manufacturer_model_name,  examination.accession_numbers as "accession_numbers",  examination.study_descriptions as "study_descriptions",  array_agg(series.dcm_series_number::text || '. ' || series.dcm_series_description    order by acquisition.acquisition_datetime) as "series_detail" from exams_with_series  inner join examination using (examination_id)  inner join acquisition using (examination_id)  inner join  dicom.series using (series_id)  left join resource_to_dicom_equipment using (dicom_equipment_id)  left join resource using (resource_id)  left join dicom.equipment_alias using (equipment_alias_id)  left join dicom.equipment using (equipment_id)  left outer join dicom.patient on dicom.patient.patient_id = examination.dicom_patient_id group by examination.examination_datetime,2,3,4,5,6,7 order by examination.examination_datetime asc; ``` ## What's next We hope this tutorial has helped you get started with querying the Quantivly data layer using SQL. There's so much more to discover, and we'll continue to add more examples and advanced topics in future updates. In the meantime, have fun exploring and experimenting with your own queries. Unlock new insights, dive deeper into your data, and make the most of this powerful tools at your disposal. > **Need help or have questions?** If you have any questions or need assistance, please don't hesitate to reach out to us at [contact-cx@quantivly.com](mailto:contact-cx@quantivly.com). We're here to support you on your data journey. **HAPPY QUERYING!** The ratio of exam time to scheduled appointment time during total operating hours during that time period, Including a patient change buffer (ie. 10min) Also known as : examination duration Definition : length of an exam, measured from the beginning of the first acquisition to the end of the last acquisition.