How to select the first date and last date of each block in SQL

Question

For the table:

date	phase	user_id
1.1.20	a	10
2.1.20	a	10
3.1.20	b	10
4.1.20	a	10
5.1.20	a	10
6.1.20	b	10

I need to return for each user_id the start date and end date for each phase when a contains all the phases in the middle and b is the end phase, without using window functions. Should look like this:

user_id	start_date	end_date
10	1.1.20	3.1.20
10	4.1.20	6.1.20

Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — pringi, Feb 18 '22 at 11:38

score 1 · Answer 1 · answered Feb 18 '22 at 12:45

Without using window functions.
You can do a self-join to the table.
Then calculate a rank/group for the phases.
Then it's simple to group by the user and the phase group.

select user_id
, min([date]) as start_date
, max([date]) as end_date
from
(
  select t1.user_id, t1.[date], t1.phase
  , count(t2.phase) as grp
  from your_table t1
  left join your_table t2
    on t2.user_id = t1.user_id
   and t2.phase = 'b'
   and t2.[date] >= t1.[date]
  group by t1.user_id, t1.[date], t1.phase
) q
group by user_id, grp
order by user_id, start_date

user_id	start_date	end_date
10	2020-01-01	2020-01-03
10	2020-01-04	2020-01-06

Test on db<>fiddle here

Meysam Asadi · Answer 2 · 2022-02-19T03:50:46.263

with cte1 as(
        select user_id,date,
         case
           when phase = 'a' and (lag(phase)over(order by date) is null or lag(phase)over(order by date) = 'b') 
            then 1
            else 0
          end grp
        from tb),
cte2 as(
    select user_id,date, sum(grp)over(order by date) as rnk
    from cte1)
select user_id,min(date)start_date,max(date)end_date
from cte2
group by user_id,rnk

EDIT: without window functions

with cte1 as(
   select *,
      case 
        when phase = 'b' then 'end'
        when phase = 'a' and 
           (select top 1 phase from tb t2 where t2.date < t1.date order by date desc) = 'a' 
          then 'no'
        else 'start' end grp
    from tb t1),
cte2 as(
    select *,
       (select top 1 date from cte1 t2 where t2.phase = 'a' and t2.date = t1.date)start_date,
       (select top 1 date from cte1 t2 where t2.phase = 'b' and t2.date = t1.date)end_date
    from cte1 t1
    where grp = 'start' or grp = 'end'),
cte3 as(
    select *
    from cte2
    where start_date is not null
),
cte4 as(
    select *
    from cte2
    where end_date is not null
)
select t1.user_id,t1.start_date,t2.end_date
from cte3 t1

cross apply 

  (select top 1 *
  from cte4 t2
  where t1.date < t2.date) t2

result

Thank you but I needed a code without window functions – pink Feb 18 '22 at 16:57 — pink, Feb 18 '22 at 16:57

How to select the first date and last date of each block in SQL

2 Answers2