My task is the following:
- Check each Doc_no to see if 'K002' and 'K110' happened.
- If both happen, I need the max(start_date) of the 'K030' which is before the start date of 'K110'
My desired results:
Doc_No Start_Date
149345 '2016-11-10'
I don't want the following because:
Doc_No Reason
149355 There is no K030 start_date before K110
149365 There is no K110
149375 There is no K030
CREATE TABLE #Status
(Doc_no int,
Wo_status varchar (4),
Start_Date datetime)
INSERT INTO #Status
Values(149345,’K002’,’2016-11-8’),
(149345,’K030’,’2016-11-9’),
(149345,’K030’,’2016-11-10’),
(149345,’K110’,’2016-11-12’),
(149345,’K030’,’2016-11-13’),
(149355,’K002’,’2016-12-1’),
(149355,’K110’,’2016-12-2’),
(149355,’K030’,’2016-12-3’),
(149365,’K002’,’2016-12-5’),
(149365,’K030’,’2016-12-6’),
(149375,’K002’,’2016-12-8’),
(149375,’K110’,’2016-12-9’)
Thanks for your help