TT Best Practice

TRANE TECHNOLOGIES SQL CHEAT SHEET

SQL (Structured Query Language) is a language used to talk with databases allowing you to select and transform the specific data you
are interested in. It is a universal language and used in most technologies that process data.

table_1 table_2 (*partitioned field)

UTILIZE FILTERS
id sbu country employee_count id sbu function cost date String
1 RHVACS NA 23,000 1 TK Finance $353,987 2023-01-01 00:00:00
Numeric SELECT sbu, cost
2 TK EMEA 3,500 2 TK IT $49,003 2023-04-17 05:42:12
SELECT sbu, function FROM table_2
3 CHVAC Null 30,642
FROM table_2 WHERE function != ‘Finance’
3 RHVACS Sales $670,854 2023-09-22 12:05:30
WHERE cost > 50000; AND sbu = ‘TK’;
… … … … … … … … …

DateTime
QUERY A SINGLE TABLE UTILIZE ALIASES
SELECT sbu, cost
Fetch all columns from a table (not Columns
best practice) FROM table_2
SELECT sbu as sbu_name
SELECT * WHERE date BETWEEN TIMESTAMP('2023-01-01 00:00:00') AND
FROM table_1;
FROM table_1; TIMESTAMP('2023-09-01 00:00:00’);
Tables
Fetch specific columns from a
SELECT a.employee_count, Filter out Nulls and by Partition Date
table
b.function SELECT a.employee_count, b.function
SELECT sbu, employee_count
FROM table_1 AS a FROM table_1 AS a
FROM table_1;
JOIN table_2 AS b JOIN table_2 AS b
Fetch specific columns and sort ON a.sbu = b.sbu; ON a.sbu = b.sbu
from a table WHERE TIMESTAMP_TRUNC(date, MONTH) BETWEEN
SELECT sbu, function TIMESTAMP('2023-11-01') AND TIMESTAMP('2023-12-01’ AND
FROM table_2 b.function IS NOT NULL;
ORDER BY cost [ASC];
Please note that the table names used in the queries should be replaced with the proper format for calling tables in BigQuery
TRANE TECHNOLOGIES SQL CHEAT SHEET
OPERATORS IN ACTION Like any letters preceding this value
SELECT sbu, function, cost
Equals or doesn’t equal FROM table_2
Operator Meaning
SELECT sbu, function WHERE sbu LIKE ‘_HVAC’;
= Is equal to FROM table_2
!= Is not equal to WHERE function != ‘Finance’ Like any values that start with r or end
<> Is not equal to AND sbu = ‘TK’; with s
> Is greater than
SELECT sbu, function, cost
Greater than or equal to FROM table_2
>= Is greater than or equal to
SELECT sbu, country WHERE sbu LIKE ‘R%’
< Is less than OR sbu LIKE ‘%s’;
FROM table_1
<= Is less than or equal to
WHERE employee_count >= 20000;
IN Is in a list of values Any and between two values
NOT IN Is not in a list of values In or not exists SELECT sbu, cost
EXISTS Is found in a result set SELECT sbu, country, FROM table_2
FROM table_1 WHERE sbu = ANY (SELECT id FROM
NOT EXISTS Is not found in a result set
WHERE sbu = IN (SELECT id FROM table_1 WHERE cost BETWEEN 10000
LIKE Is a partial match AND 50000);
table_2 WHERE date >=
NOT LIKE Is not a partial match
TIMESTAMP('2023-01-01 00:00:00’) )
BETWEEN Is between two values AND NOT EXISTS (SELECT sbu FROM All
NOT BETWEEN Is not between two values table_3 WHERE table_1.id = table_3.id) ; SELECT sbu, salary, function
ANY Matches at least one of a list of values
FROM table_4
WHERE salary > ALL(SELECT salary
ALL Matches all of a list of values
FROM table_3 WHERE role = ‘Data
Analyst’);

Please note that the table names used in the queries should be replaced with the proper format for calling tables in BigQuery
 FULL OUTER JOIN
TRANE TECHNOLOGIES SQL CHEAT SHEET Returns all rows from both tables. If there is no matching
JOINS IN ACTION row, NULLS are returned.

table_1 table_2 (*partitioned field)

id sbu country employee_count id sbu function cost date

SELECT <list>
1 RHVACS NA 23,000 1 TK Finance $353,987 2023-01-01 00:00:00
FROM table_1 A
2 TK EMEA 3,500 2 TK IT $49,003 2023-04-17 05:42:12 FULL OUTER JOIN table_2 B
3 CHVAC Null 30,642 3 RHVACS Sales $670,854 2023-09-22 12:05:30 ON A.sbu = B.sbu;
… … … … … … … … …

LEFT INNER JOIN RIGHT INNER JOIN OUTER JOIN

Returns all rows from L table with corresponding rows from Returns all rows from R table with corresponding rows from Returns only rows from the R table and L table that do not
R table. If there’s no match, all values are returned from the L table. If there’s no match, all values are returned from the have a match.
L table. R table.

SELECT <list>
SELECT <list> SELECT <list> FROM table_1 A
FROM table_1 A FROM table_1 A FULL OUTER JOIN table_2 B
LEFT JOIN table_2 B RIGHT JOIN table_2 B ON A.sbu = B.sbu
ON A.sbu = B.sbu; ON A.sbu = B.sbu; WHERE A.sbu IS NULL OR
B.sbu IS NULL;

LEFT JOIN RIGHT JOIN INNER JOIN

Returns all rows from L table with corresponding rows from Returns all rows from R table with corresponding rows from Returns all rows from R table with corresponding rows from
R table. If there’s no match, NULLS are returned as values L table. If there’s no match, NULLS are returned as values L table. If there’s no match, NULLS are returned as values
from the L table. from the R table. from both tables.
SELECT <list> SELECT <list>
SELECT <list> FROM table_1 A FROM table_1 A
FROM table_1 A RIGHT JOIN table_2 B INNER JOIN table_2 B ON
LEFT JOIN table_2 B ON A.sbu = B.sbu A.sbu = B.sbu;
ON A.sbu = B.sbu WHERE A.sbu IS NULL;
WHERE B.sbu IS
NULL;

Please note that the table names used in the queries should be replaced with the proper format for calling tables in BigQuery
TRANE TECHNOLOGIES SQL CHEAT SHEET
GET TO KNOW YOUR TOOLBAR

The DETAILS tab gives you information on the

table ID that you can copy and use in a query as
well as all the meta data on the table, so you know
the amount of data you’re working with and if there
is a partitioned field available.

The SCHEMA tab provides you

the ability to scroll through all
available fields, see their type and The PREVIEW tab allows you to see a
mode and be able to search for subset of the data (50 rows), without
specific fields of interest. being charged for running a query. This is
a great way to browse some of the values
in each of the available fields.

Example of a partitioned field Example of a clustered field

in the details tab. in the details tab.
 TT Best Practice
TRANE TECHNOLOGIES SQL CHEAT SHEET
PARTITION AND CLUSTER FIELDS IN ACTION
First you must identify if a table has a partition or cluster field. You will identify this on the “Details” tab in BigQuery. If it is partitioned, you will
see information on what grouping it is partitioned and/or clustered by and on which field(s) they are available.

ttc-scm-all-p-a001.sales_src.table_2
FILTER BY PARTITIONS (processes less data)
SELECT sbu, function, cost
FROM ‘ttc-scm-all-p-a001.sales_src.table_2’
WHERE TIMESTAMP_TRUNC(date, MONTH) BETWEEN
TIMESTAMP('2023-09-01') AND TIMESTAMP('2023-09-
01')

FILTER BY CLUSTERS (runs faster with orgid = 1 vs sbu = ‘TK’)

orgid SELECT sbu, function, cost
*cluster (*partitioned field)
FROM ‘ttc-scm-all-p-a001.sales_src.table_2’
table_2
orgid sbu function cost date
WHERE orgid = 1
1 TK Finance $353,987 2023-01-01 00:00:00

1 TK IT $49,003 2023-04-17 05:42:12

Note: When you use a clustered column in your where clause, the estimated cost will be
the cost without using it, but the actual cost will be significantly less.
2 RHVACS Sales $670,854 2023-09-22 12:05:30

… … … … …

Please note that the table names used in the queries should be replaced with the proper format for calling tables in BigQuery
TRANE TECHNOLOGIES SQL CHEAT SHEET

HOW TO FORMAT & USE FUNCTIONS IN BIGQUERY

You must call a table in this order: `project . dataset . table`
• Ex: FROM `ttc-dem-all-d-a001.service_and_support_consume.cstvnt_warranty_claim`
Function TDC SQL
Now or Current Timestamp Current_TimeStamp()

Uses Greenwich Mean Time (-1 hour difference from Irish Standard Time)
Extract Day, Month or Year WITH Datetimes AS (
Example of each displayed on 5th line SELECT DATETIME '2005-01-03 12:34:56' AS datetime)

SELECT
datetime,
EXTRACT(Day FROM datetime) AS Day, | EXTRACT(Month FROM datetime) AS Month, | EXTRACT(Year FROM datetime) AS Year,
FROM Datetimes
Date Format in YYYY-MM-DD HH:MI:SS fav.attribute_category_code,
format_date('%F %T', Current_TimeStamp()) gl_data_as_of
FROM `ttc-fin-all-d-a001-044d.accounting_raw.irebs_gl_je_lines` jl
Date Subtract Last X Days and Current_TimeStamp() Between COALESCE(src_irebs_po_headers_all.start_date, Current_TimeStamp())
and COALESCE(src_irebs_po_headers_all.end_date, DATE_SUB(Current_TimeStamp(),INTERVAL -1 DAY))
and Current_TimeStamp() < COALESCE(src_irebs_po_lines_all.expiration_date, DATE_SUB(Current_TimeStamp(), INTERVAL -1
DAY))
Date Addition Next X Days TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
NVL or IF NULL Function COALESCE(src_irebs_po_headers_all.start_date, Current_TimeStamp())
CAST as Another Datatype Cast(Column_name as string)
UNION (with distinct values from queries) UNION DISTINCT

Last 7 Days TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

Date Function DATE(date_created) between '2023-01-01' and '2023-01-08'
TRANE TECHNOLOGIES SQL CHEAT SHEET
KNOWN ISSUES IN BIGQUERY
• Google BigQuery does not have a built-in NVL function. However, you can achieve similar functionality using the IFNULL
or COALESCE functions (conversation about this: NVL to COALESCE or IS NULL.mp4).
• Not all tables have partitioned fields, if a user would like to add a partitioned field, please reach out to
kirk_vilhauer@tranetechnologies.com or rajashekar.penumarthy@tranetechnologies.com.
• Possible issues if using NOT IN function: https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-
legacy-sql#not_in_conditions_and_null.

REFERENCE LINKS:
• Alteryx app created by Trevor Sutherland to help change a query over from EDH to TDC: Changeover App
• Trane Technologies Data Cloud (TDC) - Getting Started Checklist
• Big Query SQL Best practices.docx (sharepoint.com)
• Functions, operators, and conditionals | BigQuery | Google Cloud
• Date functions | BigQuery | Google Cloud
• For conversion of more functions from Impala to BigQuery, click here for documentation
• GCP Query Cost Report
• TDC Table Load Dashboard
• SQL FAQ Recordings from our Alteryx Office Hours