I am trying my hardest but I still cannot seem to get this right.
Looking at the table below, you can think of what weekdays a certain company does deliveries and the various cutoff-times they use for incoming orders. Simply, I want "desired_outcome(next delivery)" to show what weekday the next delivery will be, based on when cutoff1 is not null.
So looking at Company A for example, if an order was to be placed later than 20:00 on a Sunday, it would be delivered on Monday instead since they do deliveries on Mondays (cutoff1 is not null). However, if someone would place an order on Monday after 23:30 or whenever on Tuesday, they would have to wait until Wednesday when the company does deliveries again. And if the customer was to place an order later than 18:00 on Saturdays, they would have to wait until Sunday. The problem here is that I have tried to use LEAD() but that will only fetch the next value, even when cutoff1 is null. I want the function to fetch "the next" weekday where cutoff1 is not null, so pretty much a windowfucntion (lead) but with conditions. Give me the next value only if cutoff1 is not null. And when lead () returns null which it will at the last row, I need it to "start from the beginning of the list" and fetch the first weekday where cutoff is not null.
Any ideas :) ? This is really doing my head in.
Example:
| Merchant_name | weekday | cutoff1 | cutoff2 | cutoff3 | desired_outcome (next delivery) |
|---|---|---|---|---|---|
| Company A | 0 | 13:00 | 15:00 | 20:00 | 1 |
| Company A | 1 | 13:00 | 15:00 | 23:30 | 3 |
| Company A | 2 | NULL | NULL | NULL | 3 |
| Company A | 3 | 13:00 | 15:00 | 19:00 | 4 |
| Company A | 4 | 13:00 | 15:00 | 18:00 | 5 |
| Company A | 5 | 13:00 | 15:00 | 18:00 | 6 |
| Company A | 6 | 13:00 | 15:00 | 18:00 | 0 |
| Company B | 0 | NULL | NULL | NULL | 1 |
| Company B | 1 | 13:00 | 15:00 | 23:30 | 3 |
| Company B | 2 | NULL | NULL | NULL | 3 |
| Company B | 3 | 13:00 | 15:00 | 19:00 | 5 |
| Company B | 4 | NULL | NULL | NULL | 5 |
| Company B | 5 | 13:00 | 15:00 | 18:00 | 6 |
| Company B | 6 | 13:00 | 15:00 | 18:00 | 1 |
Possibly the following is what you need, using a correlated subquery to select the next qualifying weekday:
select *, (
select Coalesce(Min(case when t2.weekday > t.weekday then t2.weekday end) over(), t2.weekday)
from t t2
where t2.Merchant_name=t.Merchant_name
and t2.cutoff1 is not null
limit 1
) Outcome
from t;