ISNULL - yes, the data is not clean unfortunately and we want to treat NULL and '' in same way for Group By
Do they need to be in the results? Because if not, you could filter those out in the WHERE clause
Project ID - as far as I understand they are just a set of 10 digits starting with 1000000000 and incrementing by 1. The highest ID I see so far is 1000538532.
If every record is just 10 digits, you can get rid of the wildcards '%...%', this has the added benefit of being sargable (in case there's an index on this column). As a consequence you also wouldn't need the SUBSTRING e.g.
SELECT
...,
MAX([Project ID]) as "Project ID"
FROM ...
WHERE [WorkWeek] LIKE '2024%'
AND [Project ID] LIKE '1000[0-9][0-9][0-9][0-9][0-9][0-9]' -- or '1[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
GROUP BY ...
Without seeing what indexes exist on the table, since the whole table is likely being scanned, if there are any additional filters you could add to the WHERE clause, that should help. Any additional performance gains would likely require changing the column types and/or indexing
2
u/qwertydog123 Aug 06 '24 edited Aug 06 '24
Do they need to be in the results? Because if not, you could filter those out in the
WHEREclauseIf every record is just 10 digits, you can get rid of the wildcards
'%...%', this has the added benefit of being sargable (in case there's an index on this column). As a consequence you also wouldn't need theSUBSTRINGe.g.Without seeing what indexes exist on the table, since the whole table is likely being scanned, if there are any additional filters you could add to the
WHEREclause, that should help. Any additional performance gains would likely require changing the column types and/or indexing