Date: Tue, 19 Mar 2024 12:28:35 +0000 (UTC) Message-ID: <2062612945.2185.1710851315822@docs.stiltsoft.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_2184_661951635.1710851315821" ------=_Part_2184_661951635.1710851315821 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Use case:
You have a table with the stationary orders. You need to output a n= ew column in the table with the order priority according to the total sum o= f money: less than $1000 is Low, from $1000 to $2000 is Medium, and more th= an $2000 is High.
Solution:
Enter the following SQL query:
SELECT *, CASE WHEN 'Subtotal' < 1000=20 THEN "LOW"=20 WHEN 'Subtotal' >=3D 1000=20 AND 'Subtotal' < 2000=20 THEN "MEDIUM"=20 ELSE "HIGH"=20 END AS 'Priority'=20 FROM T1
CASE WHEN ... THEN ... EL= SE ... END goes through conditions and return a value when th= e first condition is met.
AS '...' outputs a n= ew 'Priority' column.If you want to replace the words Low, Medium and High by prominent statu= ses 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 ma= cro body. Don't change anything in the SQL query.
Use case:
You have a table with two columns: Target date and Completion date. You = need to compare and rate these dates:
Solution:
Enter the following SQL query:
SELECT *= , CASE WHEN (('Completion date' - 'Target date') / "24h") < 5=20 THEN "ON TIME"=20 WHEN (('Completion date' - 'Target date') / "24h") > 5=20 AND=20 (('Completion date' - 'Target date') / "24h") <=3D 10=20 THEN "LATE"=20 ELSE "VERY LATE"=20 END AS 'Rating'=20 FROM T1
Use case:
You have a table with the number of added lines of code and the number o= f defects. You need to calculate the quality of the code. The number of def= ects less than one defect per 10 lines indicates the good quality of the co= de. Otherwise the quality of the code is bad.
Solution:
Enter the following SQL query:
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=20 THEN "Good"=20 ELSE "Bad"=20 END AS 'Code quality'=20 FROM T1
('...' * 10 / '...') = ;AS '...' calculates the number of def= ects per 10 lines of code and outputs the 'Defects per 10 lines' column.&nb= sp;
CASE WHEN ... THEN ... ELSE ... END AS '...'= goes through conditions and return a value when the first co= ndition is met and outputs the 'Code quality' column.
You can merge two columns containing text with the help of formulas:
SELECT *, ( 'First name' + " " + 'Last name' ) AS 'Full name'=20 FROM T1
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:
SELECT *=20 FROM T1=20 JOIN T2=20 ON T1.'Transaction ID' =3D T2.'Transaction ID'=20 WHERE ( 'Transaction Date' >=3D "10 / 1 / 2018"=20 AND 'Transaction Date' < "11 / 1 / 2018" ) AND=20 ( 'Customer Type' =3D "Business" )
WHERE ('...' >=3D "10/= 1/2018" AND '...' < "11/1/2018") AND ('...' =3D "...") ext= racts only those records that fulfill a specified condition.
Use case:
You have a table containing a period of time (start date and end date).<= /p>
You need to count the number of workdays during this time period.
Advanced use case:
You have an additional table with national holidays which you also shoul= dn't consider as workdays.
Solution:
Enter the following SQL query:
SELECT *= , 'Days' - 2 * (('Days' / 7)::integer) - CASE =09WHEN 'Days' % 7 =3D 0 THEN 0 =09WHEN 'Start date'::Date->getDay() =3D 0 THEN 1 =09WHEN 'Start date'::Date->getDay() + 'Days' % 7 =3D 6 THEN 1 =09WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 6 THEN 2 =09ELSE 0 END AS 'Work days' FROM (SELECT *, (T1.'End date' - T1.'Start date') / "1d" + 1 AS 'Days' FROM T1)
Solution for the advanced use case:
Enter the following SQL query:
SELECT *= , 'Days' - 2 * (('Days' / 7)::integer) - CASE =09WHEN 'Days' % 7 =3D 0 THEN 0 =09WHEN 'Start date'::Date->getDay() =3D 0 THEN 1 =09WHEN 'Start date'::Date->getDay() + 'Days' % 7 =3D 6 THEN 1 =09WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 6 THEN 2 =09ELSE 0 END -=20 (SELECT COUNT(*) FROM=20 (SELECT *,'Holiday'::Date->getDay() AS 'Day' FROM T2)=20 WHERE 'Holiday' >=3D TT.'Start date' AND 'Holiday' <=3D TT.'End date' AND 'Day' > 0 AND 'Day' < 6) AS 'Work days' FROM (SELECT *, ('End date' - 'Start date') / "1d" + 1 AS 'Days' FROM T1) AS TT<= /pre>