I am trying to display stores that haven't updated their merchandise inventory in x number of days.
The District Manager wants to enter a number (like 90 or 365) and get a list of all the stores AND the last inventory they ordered. So, if 90 stores haven't ordered anything in the last 90 days, the store name and all the items they last ordered need to be displayed.
If I use this:
WHERE MerchLastShippedDate = DATEADD("d", -@Days, CONVERT(VARCHAR(10),Getdate(),101))
my query runs in an ok amount of time. But, that isn't showing me everything I need. It's when I use the below to show me everything that is older than the number of days I enter that the query takes too long to run.
How can I correct this and make my query run faster?
Here is my code:
IF object_id('tempdb..#ActiveStores') is not null
BEGIN
DROP TABLE #ActiveStores
END
SELECT DISTINCT
Storename,
StoreId
INTO #ActiveStores
FROM
StoresTable
Where Storestatus = 1
and Storename <> ' '
Order by
StoreID
IF object_id('tempdb..#MerchDays') is not null
BEGIN
DROP TABLE #MerchDays
END
Create Table #MerchDays
(StoreID varchar(100),
StoreName varchar(100),
MerchID varchar(50),
Manager varchar(100),
MerchLastShippedDate datetime,
MerchNumber varchar(100),
QtyMerchShipped float,
AmtMerchShipped float
)
INSERT INTO #MerchDays
(StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped)
SELECT StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped
FROM MainStoredInventoryMerchTbl
WHERE MerchLastShippedDate <= DATEADD("d", -@Days, CONVERT(VARCHAR(10),Getdate(),101))
GROUP BY StoreID, StoreName, MerchId, Manager, MerchLastShippedDate, MerchNumber, QtyMerchShipped, AmtMerchShipped
ORDER BY StoreID
</<pre lang="SQL">
What I have tried:
MSDN, Google search, stackoverflow