CODEWITHSUNDEEP
sqlsql-server
Roger Steinberg
Roger Steinberg

Reputation: 1604

How to extract rows based on column's sequence of occurences

I need to extract all the rows associated to their respective Ids that have 'In' in the type column that occured before 'Out' using the date column. In the data provided, only Id 1 & 2 would pass the test. Test data is as follows:

CREATE TABLE #Table (
id INT,
[type] varchar(25),
[Dates] Date)

INSERT INTO #Table
VALUES (1, 'In', '2018-10-01'),
 (1, 'In', '2018-11-01'),
 (1, 'Out', '2018-12-01'),
 (2, 'In', '2018-10-01'),
 (2, 'Out', '2018-11-01'),
 (2, 'In', '2018-12-01'),
 (3, 'Out', '2018-10-01'),
 (3, 'In', '2018-11-01')

The ouput should look like that:

+----+------+------------+
| id | type |    date    |
+----+------+------------+
|  1 | In   | 2018-10-01 |
|  1 | In   | 2018-11-01 |
|  1 | Out  | 2018-12-01 |
|  2 | In   | 2018-10-01 |
|  2 | Out  | 2018-11-01 |
|  2 | In   | 2018-12-01 |
+----+------+------------+

Im honestly lost with querying this issue. I started with 

SELECT #Table.*, MIN(CASE WHEN #Table.[type] = 'In' THEN #Table.Dates ELSE NULL END) As A
    ,MIN(CASE WHEN #Table.[type] = 'Out' THEN #Table.Dates ELSE NULL END) As B
FROM #Table
GROUP BY #Table.id, #Table.[type], #Table.Dates

Not sure what to do from there...

Upvotes: 3

Views: 109

Answers (4)

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try the following using inner join as shown below.

CREATE TABLE #Table (
id INT,
[type] varchar(25),
[Dates] Date)

INSERT INTO #Table
VALUES (1, 'In', '2018-10-01'),
 (1, 'In', '2018-11-01'),
 (1, 'Out', '2018-12-01'),
 (2, 'In', '2018-10-01'),
 (2, 'Out', '2018-11-01'),
 (2, 'In', '2018-12-01'),
 (3, 'Out', '2018-10-01'),
 (3, 'In', '2018-11-01')

 SELECT DISTINCT #Table.* FROM(
SELECT  * FROM #Table
)a inner join (
SELECT * FROM #Table
)b on a.id = b.id and a.Dates < b.Dates and a.[type] = 'In' and b.[type] = 'Out'
inner join #Table on a.id = #Table.id

You can see the below output as required 

id  type    Dates
 --------------------
 1  In  2018-10-01
 1  In  2018-11-01
 1  Out 2018-12-01
 2  In  2018-10-01
 2  In  2018-12-01
 2  Out 2018-11-01

Upvotes: 2

level3looper
level3looper

Reputation: 1041

This could work for you:

With CTE As
 (
   Select Id,[Type], [Dates], ROW_NUMBER() OVER(Partition By Id Order By Dates) As rn From #Tbl
 )
 Select * From cte
 Where cte.Id In (Select Distinct Id From CTE Where rn = 1 And [Type] = 'In')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

If I understand correctly, aggregation and having do the trick:

select t.id
from #Table t
group by t.id
having min(case when type = 'In' then dates end) < max(case when type = 'Out' then dates end);

This selects ids where the earliest "in" is before the latest "out".

If you need the matching rows, you can use window functions, in, exists or a join:

select t.*
from #table t
where t.id in (select t2.id
               from #Table t2
               group by t2.id
               having min(case when t2.type = 'In' then t2.dates end) < max(case when t2.type = 'Out' then t2.dates end)
              );

Upvotes: 3

dnoeth
dnoeth

Reputation: 60472

This is Gordon's logic applied to your query using Windowed Aggregates:

with cte as 
 (
    SELECT #Table.*
        ,MIN(CASE WHEN #Table.[type] = 'In' THEN #Table.Dates ELSE NULL END)
         OVER(PARTITION BY id) As A -- min IN date per id
        ,MAX(CASE WHEN #Table.[type] = 'Out' THEN #Table.Dates ELSE NULL END)
         OVER(PARTITION BY id) As B -- max OUT date per id
    FROM #Table
 )
select *
from cte
where a < b

Of course can also use a subquery:

select *
from #Table
where id in 
 ( Gordon's Select )

Upvotes: 2

Related Questions