I have just discovered that my favourite function exists in SQL.
Select Case is to me, the best thing ever. Elegant, simple and powerful. Makes short work of processing and sorting in most scripts and programs.
And today – whilst writing some queries for reports, I have now found that it exists in SQL!
My sample below shows what I have made work here. Think this could be extended a lot further to make really powerful Select statements. Going to have to give it a try some time.
The whole statement is constructed in much the same way as when programming with scripts.
SELECT DATEPART(yy, RecordTable.date) AS Year, DATEPART(wk, RecordTable.date) AS ‘Week’, COUNT(RecordTable.ID) AS Incidents,
CASE
WHEN Descript.Action LIKE ‘%racist%’ THEN ‘Racist Incident’
WHEN Descript.Action LIKE ‘%bully%’ THEN ‘Bullying Incident’
ELSE ”
END AS Category
Easy. Just saved me a load of time with filters too.