Multiple criteria

Transact-SQL
1

My task is the following:

  1. Check each Doc_no to see if 'K002' and 'K110' happened.
  2. 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

2

Try this:

select a.doc_no
      ,max(b.start_date) as start_date
  from #status as a
       inner join #status as b
               on b.doc_no=a.doc_no
              and b.wo_status='K030'
              and b.start_date<a.start_date
 where a.wo_status='K110'
   and exists (select 1
                 from #status as b
                where b.doc_no=a.doc_no
                  and b.wo_status='K002'
              )
 group by a.doc_no
;
1 Like
3 
SELECT derived.Doc_no, MAX(s_k030.Start_Date) AS Start_Date
FROM (
    SELECT 
        Doc_no,
        MAX(CASE WHEN Wo_status = 'K110' THEN start_date END) AS K110_start_date
    FROM #Status
    WHERE Wo_status IN ('K002', 'K030', 'K110')
    GROUP BY Doc_no
    HAVING 
        MAX(CASE WHEN Wo_status = 'K002' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN Wo_status = 'K030' THEN 1 ELSE 0 END) = 1 AND
        MAX(CASE WHEN Wo_status = 'K110' THEN 1 ELSE 0 END) = 1
) AS derived
INNER JOIN #Status s_k030 ON 
    s_k030.Doc_no = derived.Doc_no AND 
    s_k030.Wo_status = 'K030' AND 
    s_k030.Start_Date < derived.K110_start_date
GROUP BY derived.Doc_no
1 Like
4

Thank you both. I can work with this.