I have a query which uses a WHERE
clause, and I happen to use the exact same WHERE
clause in many queries on this table (et al).
The query is:
SELECT
DATENAME(DW, [AtDateTime]) AS [Day of Week]
,COUNT(*) AS [Number of Searches]
,CAST(CAST(COUNT(*) AS DECIMAL(10, 2))
/ COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2))
AS [Average Searches per Day]
,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
AS [Number of Searches with no Results]
,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END)
AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4))
AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory]
WHERE
[CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])
The part I wish to change is the WHERE
clause, to instead allow me to use a table so that if I have to add a customer number to be ignored, I don't have to update all my queries. (And there are quite a few queries that have this same WHERE
clause.)
Best Answer
Create a table to hold the customer numbers to be excluded, then exclude those rows using a
NOT EXISTS
in theWHERE
clause.