Hi @Eirikur Eiriksson, I'm familiar with that post.  Bascially it says that everytime that getdate() runs the value will be slightly different...

pietlinden,  I appreciate the help and intro to LAG. It would have worked if ship_dates were never more than one day apart, as I had presented my DDL.

In...

@Rinu Philip
if I know how many users will connect to the query (running SSRS report and pass parameters) how can I test whether the performance will...

Yes, the most rows I expect returned at any one run is 200 (or really radically, 1000), and that should be unusual.

Yes! @jonathan-2 AC Roberts  I was just heading over to add the Force Seek to the UNIONs as one more tweak to my last post, when I see...

@frederico_fonseca , @jonathan-2 AC Roberts and ChrisM@Work

Corrected
(1)
FROM DistributionCenterFeed.[dbo].[QR_Device_Trade_In_Master_2016onward] QR
WHERE QR.[Offer_Generate_Date] >= @FROM_DATE AND QR.[Offer_Generate_Date] <= @TO_DATE;
(2)
I dropped [<Name...

one more thing. when I ran the query this last time SSMS advised this index, but then wouldn't I need to create a different INCLUDE for each of the other possible...

@jonathan-2 AC Roberts
I used Generate Scripts to script out all indexes created for this table, and it includes the non clustered you mentioned (different names/ they already...

@frederico_fonseca - I will send the query and execution plan in private email tonight when server less busy. This morning the CTE query isn't completing, going 9 min.  In addition...

Very exciting development. The specific UNION solution Jonathan AC Roberts supplied is currently (it's midnight) is showing a lot of promise, between lightening fast. 00:00:00 ms and 9 sec, when I pass BulkID or...

Answers to Qs:
Grant Fritchey: (1) I added parenthesis around Date.  (2) using parameters. Attached query.
ChrisM: (1) table has 98 columns (2) selectivity of columns will change over time (3) rows returned...