I am looking for a high-performance approach to solve this problem.
I have a table on a SQL-Server 2019 with 316 columns and several million rows.
For this query, there are only a few of those columns that are relevant. Something like a date-field and an amount-field (maybe the id-field as well).
Now I would like to know how many entries exist in a specific date range (from now, to X days in the past) that has an amount that is lower than Y.
My current approach (split the request into two requests):
Create a nonclustered index over the date-field & id-field.
Do a first select with a where-clause to get only entries in the relevant date range. (Select only id) Will this avoid a full-table scan?
Do a second select (count) with a where-clause over the ids (and maybe the amount-field). The id-field is also the primary key and clustered index.
At step three I am not really sure if I can put the amount-field into the where-clause without any performance loss. If that is not possible, maybe I should select only the ids and the amount-field and do the count in my application (in memory)? It would be great if it is possible to avoid this.
Also, I am not sure if it will be necessary to split the request at step three if I will have 1,000,000 Ids which will hit the condition from step two.
What do you think about my approach? Should I go for it, or is there a better one?