Hello
Our company is in the garment hire business so we have items on an order that go out and then come back into our warehouse. When they come back their barcode is scanned at various stations within the warehouse. I want to flag items that are overdue (have not came back when they should have) and other statuses so my logic is to look at when the item was meant to be returned (OrderItems.ReturnDate) and find the next scan date we have on the system BarcodeScanHistory.DateTimeScan i can then do a datediff to workout what the status should be Overdue, long Overdue etc. My problem is performance. Each barcode can have up to 50 barcodescanhistory records so currently i'm doing a join on barcode and with DateTimeScan > returndate and taking the min date. doing this for 70k is quite slow. Below is a script to build the tables. the last query is the only way i can think of doing this so was looking for a better solution. The tables have no index or constraints currently but will have once i have a more elegant solution
--table for holding all the scans of a barcode over its lifetime
CREATE TABLE [dbo].[BarcodeScanHistory](
[DateTimeScan] [datetime] NOT NULL,
[Barcode] nvarchar NOT NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2015-04-10','21431791')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2015-04-11','21431791')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2015-04-12','21431791')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2016-04-10','01756395')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2016-04-11','01756395')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2016-04-12','01756395')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2017-04-01','00297271')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2017-04-06','00297271')
INSERT INTO [dbo].[BarcodeScanHistory]VALUES('2017-04-09','00297271')
--table for holding items that go out and come back on orders
CREATE TABLE [dbo].[OrderItems](
[ItemNumber] NVARCHAR NOT NULL,
[ReturnDate] [DATE] NULL,
[Barcode] NVARCHAR NULL
) ON [PRIMARY]
INSERT INTO [dbo].[OrderItems]VALUES('T3444083','2015-04-10','21431791')
INSERT INTO [dbo].[OrderItems]VALUES('T3443206','2016-04-11','01756395')
INSERT INTO [dbo].[OrderItems]VALUES('T3444641','2017-04-09','00297271')
--return all the barcodes and the next date the system saw the items
SELECT
oi.*,
(SELECT CAST(MIN(gp.DateTimeScan) AS DATE) FROM [dbo].[BarcodeScanHistory] gp WHERE gp.Barcode = oi.Barcode AND gp.DateTimeScan > oi.[ReturnDate]) [NextScanDate]
FROM
[dbo].[OrderItems] oi