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

user_10655190 0
Viewed 61

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...

sql-server sql
4 Answers

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)
              );
user_11440350

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 )
user_25279050

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
user_105325000

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')
user_106140910
Related