Use case:
You have a table with the stationary orders. You need to output a new column in the table with the order priority according to the total sum of money: less than $1000 is Low, from $1000 to $2000 is Medium, and more than $2000 is High.
Solution:
Enter the following SQL query (in one line as shown in the screenshot):
SELECT *, CASE WHEN 'Subtotal' < 1000 THEN "LOW" WHEN 'Subtotal' >= 1000 AND 'Subtotal' < 2000 THEN "MEDIUM" ELSE "HIGH" END AS 'Priority' FROM T1 |
CASE WHEN ... THEN ... ELSE ... END goes through conditions and return a value when the first condition is met.
AS '...' outputs a new 'Priority' column.If you want to replace the words Low, Medium and High by prominent statuses using the default Status macro or the Handy Status macro, just place a one-column table containing each status and the same column label in the macro body. Don't change anything in the SQL query. |
Use case:
You have a table with the number of added lines of code and the number of defects. You need to calculate the quality of the code. The number of defects less than one defect per 10 lines indicates the good quality of the code. Otherwise the quality of the code is bad.
Solution:
Enter the following SQL query (in one line as shown in the screenshot):
SELECT *, ( 'Number of defects' * 10 / 'Lines of code added' ) AS 'Defects per 10 lines', CASE WHEN ( 'Number of defects' * 10 / 'Lines of code added' ) < 1 THEN "Good" ELSE "Bad" END AS 'Code quality' FROM T1 |
('...' * 10 / '...') AS '...' calculates the number of defects per 10 lines of code and outputs the 'Defects per 10 lines' column.
CASE WHEN ... THEN ... ELSE ... END AS '...' goes through conditions and return a value when the first condition is met and outputs the 'Code quality' column.You can merge two columns containing text with the help of formulas:
|
Use case:
You have two tables where the columns differ.
You need to merge two tables where the entrys meet the conditions:
Solution:
Enter the following SQL query (in one line as shown in the screenshot):
SELECT * FROM T1 JOIN T2 ON T1.'Transaction ID' = T2.'Transaction ID' WHERE ( 'Transaction Date' >= "10 / 1 / 2018" AND 'Transaction Date' < "11 / 1 / 2018" ) AND ( 'Customer Type' = "Business" ) |
WHERE ('...' >= "10/1/2018" AND '...' < "11/1/2018") AND ('...' = "...") extracts only those records that fulfill a specified condition.