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?
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;