case when... for subgroups

Question

I have table like this:

receipt	position	unit	booking time
1	1	1	08:00:00
1	2	1	08:00:05
1	3	1	08:00:11
1	4	1	08:00:18
1	5	1	08:00:21
1	6	5	08:00:25
1	1	1	08:00:30
1	2	1	08:00:33
1	3	1	08:00:37
1	4	1	08:00:40
1	5	1	08:00:49
2	1	1	08:01:55
2	2	1	08:01:58
2	3	1	08:02:04
3	1	1	08:02:20
3	2	5	08:02:24
3	1	1	08:02:30
3	2	1	08:02:35

I want to check for every receipt whether unit 5 exists or not. If unit 5 exists, I only want to select positions with a booking time after the entry with unit 5.

For the example above my result therefore should look like this:

receipt	position	unit	bookingtime
1	1	1	08:00:30
1	2	1	08:00:33
1	3	1	08:00:37
1	4	1	08:00:40
1	5	1	08:00:49
2	1	1	08:01:55
2	2	1	08:01:58
2	3	1	08:02:04
3	1	1	08:02:30
3	2	1	08:02:35

I have kind of a start, which delivers the right result if there was only one receipt:

Select * from test
where bookingtime> (case
                when (select Max(bookingtime) from test where unit=5) is null
                then (Select convert(time,'00:00:00'))
                Else (select Max(bookingtime) from testdb where unit=5)
                End)

What am I missing to let this code run through every single receipt separately so that I get the result I am looking for?

Answer 1

You can use a window function to get the time for unit 5:

select t.*
from (select t.*,
             min(case when unit = 5 then bookingtime end) over (partition by receipt) as bookingtime_5
      from t
     ) t
where bookingtime_5 is null or
      bookingtime > bookingtime_5;