September 20, 2018 at 9:29 am
I have two different tables in SQL. One of which records when a product was removed from being in use and the other records when a product was cleaned. These tables contain the following fields...
tbtRemovals
SerialNumber, ProductType, DateRemoved
tblCleans
SerialNumber, ProductType, DateCleaned
I need to find out when the next clean date was after a product was removed. Each table will have the same serial number many times. So for example, serial number 12345 could have many records of when it was removed from in use and many records of when it was cleaned.
As an example, if serial number 12345 was removed on 01/01/2018 and 07/01/2018 and was cleaned on 05/01/2018 and 09/01/2018 I need to match the first cleaning date after the removal date so I can work out how many days it took to clean the product. The results of which would be
SerialNumber, DateRemoved, DateCleaned, DaystoClean
12345 01/01/2018 05/01/2018 4
12345 07/01/2018 09/01/2018 2
Any advice to help achieve this using T-SQL would be greatly appreciated.
September 20, 2018 at 1:29 pm
You can use OUTER APPLY
FROM tblRemovals r
OUTER APPLY (SELECT TOP 1 DateCleaned FROM tblCleans c WHERE c.SerialNumber = r.SerialNumber AND DateCleaned >= DateRemoved ORDER BY DateCleaned) AS cln
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 21, 2018 at 2:19 am
Awesome, thank you for your help. That has done the trick.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply