My district manager wants a report to show all stores that haven't ordered merchandise from the distribution warehouse in x number of days.
There is just one table with hundreds and of thousands of records.
The table has an OrdCreateDt field. I am thinking I need to query this table where the OrdCreateDt field is <= whatever number of days the district mgr enters.
But, the dist mgr wants to see the store numbers that did not have orders within these same number of days and the details of what the ordered before the x number of days.
I created this query:
select t.storenum, t.storename, t.mercnum, t.ordcreatedt from all_orders t left join (select storenum from all_orders where OrdCreateDt <= dateadd("dd", -days, convert (varchar(10), getdate(),101)) ) t2 on t1.storenum =t2 storenum where OrdCreateDt < dateadd("dd", -days, convert(varchar(10), getdate(),101))
This takes several seconds to run. Is this the most efficient way to do this?
What I have tried:
Msdn search, Google search, stackoverflow website