How do I detect the first row over a partition?

Question

I have a series of ingress and egress data, based upon the following common table expression. I

WITH CTE AS (  
SELECT '2020-11-02 06:00:34.000' ActionDate,	1 IsIngress UNION ALL  
SELECT '2020-11-02 06:01:15.000' ActionDate,	1 IsIngress UNION ALL  
SELECT '2020-11-02 08:26:38.000' ActionDate,	0 IsIngress UNION ALL  
SELECT '2020-11-02 08:29:26.000' ActionDate,	1 IsIngress UNION ALL  
SELECT '2020-11-02 11:41:44.000' ActionDate,	0 IsIngress UNION ALL  
SELECT '2020-11-02 11:42:39.000' ActionDate,	1 IsIngress UNION ALL  
SELECT '2020-11-02 11:48:17.000' ActionDate,	1 IsIngress UNION ALL  
SELECT '2020-11-02 16:21:53.000' ActionDate,	0 IsIngress)  
  
SELECT *, ROW_NUMBER() OVER(PARTITION BY isIngress order by ActionDate) AS  PartitionRanking     
FROM CTE ORDER BY CTE.ActionDate   

Here is what happens and what I would like to do? Get the first ingress and last egress of a "logical group" of ingresses/egresses. The day starts with an ingress. Then, I want the first ingress (in case they double swipe) and the last ingress of the first logical grouping. Then, when they come back from lunch, I want the first ingress and last egress before their break. Finally, I want their first ingress after break and their last egress for the day.

Once I get the PartitionRank to always start at (1) once it detects the IsIngress flag changing, I can filter out all the PartitionRank greater than 1 to get the desired results. So, the problem is, how do I get each cluster of ingresses and egress to start over with a PartitionRank of (1) ?

Answer 1

Hi @Rob Jarrett ,

Please refer below:

WITH CTE AS (  
 SELECT '2020-11-02 06:00:34.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 06:01:15.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 08:26:38.000' ActionDate,    0 IsIngress UNION ALL  
 SELECT '2020-11-02 08:29:26.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 11:41:44.000' ActionDate,    0 IsIngress UNION ALL  
 SELECT '2020-11-02 11:42:39.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 11:48:17.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 16:21:53.000' ActionDate,    0 IsIngress)  
      
 select ActionDate, IsIngress,  
       row_number() over (partition by IsIngress, grp order by ActionDate) as row_num  
from (select t.*,  
             (row_number() over (order by ActionDate) -  
              row_number() over (partition by IsIngress order by ActionDate)  
             ) as grp  
      from CTE t  
     ) t  
  order by ActionDate  

Output:

ActionDate IsIngress row_num  
2020-11-02 06:00:34.000 1 1  
2020-11-02 06:01:15.000 1 2  
2020-11-02 08:26:38.000 0 1  
2020-11-02 08:29:26.000 1 1  
2020-11-02 11:41:44.000 0 1  
2020-11-02 11:42:39.000 1 1  
2020-11-02 11:48:17.000 1 2  
2020-11-02 16:21:53.000 0 1  

The final:

;WITH CTE AS (  
 SELECT '2020-11-02 06:00:34.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 06:01:15.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 08:26:38.000' ActionDate,    0 IsIngress UNION ALL  
 SELECT '2020-11-02 08:29:26.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 11:41:44.000' ActionDate,    0 IsIngress UNION ALL  
 SELECT '2020-11-02 11:42:39.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 11:48:17.000' ActionDate,    1 IsIngress UNION ALL  
 SELECT '2020-11-02 16:21:53.000' ActionDate,    0 IsIngress)  
 ,cte2 as (     
 select ActionDate, IsIngress,  
       row_number() over (partition by IsIngress, grp order by ActionDate) as row_num  
from (select t.*,  
             (row_number() over (order by ActionDate) -  
              row_number() over (partition by IsIngress order by ActionDate)  
             ) as grp  
      from CTE t  
     ) t)  
select * from cte2 where row_num=1 order by ActionDate  

Output:

ActionDate IsIngress row_num  
2020-11-02 06:00:34.000 1 1  
2020-11-02 08:26:38.000 0 1  
2020-11-02 08:29:26.000 1 1  
2020-11-02 11:41:44.000 0 1  
2020-11-02 11:42:39.000 1 1  
2020-11-02 16:21:53.000 0 1  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Check if the next query works with various data:

WITH CTE AS (
    SELECT '2020-11-02 06:00:34.000' ActionDate,    1 IsIngress UNION ALL
    SELECT '2020-11-02 06:01:15.000' ActionDate,    1 IsIngress UNION ALL
    SELECT '2020-11-02 08:26:38.000' ActionDate,    0 IsIngress UNION ALL
    SELECT '2020-11-02 08:29:26.000' ActionDate,    1 IsIngress UNION ALL
    SELECT '2020-11-02 11:41:44.000' ActionDate,    0 IsIngress UNION ALL
    SELECT '2020-11-02 11:42:39.000' ActionDate,    1 IsIngress UNION ALL
    SELECT '2020-11-02 11:48:17.000' ActionDate,    1 IsIngress UNION ALL
    SELECT '2020-11-02 16:21:53.000' ActionDate,    0 IsIngress),
cte2 as
(
    select *, 
        isnull(LAG(IsIngress) over(order by ActionDate), 0) as prev, 
        isnull(LEAD(IsIngress) over(order by ActionDate), 1) as next 
    from CTE
)
select ActionDate, IsIngress
from cte2 
where IsIngress = 1 and prev = 0 or IsIngress = 0 and next = 1
order by ActionDate