1

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
Robin Lindström
  • 13
  • 2
  • I would just use a correlated subquery. Are all 3 cutoff columns always NULL together? – Stu Mar 24 '22 at 15:12
  • If cutoff1 is null, all of them will be since cutoff1 is fetched from a CTE before this one. Simply showing the first cutoff if there is any, regardless of time. – Robin Lindström Mar 24 '22 at 18:10

1 Answers1

0

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;
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Your query misses the fact that company B don't take deliveries on day 0. It's the only line which is false. If you want to modify your answer then I'll delete mine, if not I'll leave it with credits to you. Regards Ken –  Mar 24 '22 at 15:56
  • Thanks yes I must have missed that for the coalesce, have amended and simplified. – Stu Mar 24 '22 at 16:44
  • That's because you are trying it on 5.7 - however OP mentions using window functions so is using 8+ – Stu Mar 24 '22 at 17:00
  • works fine on mySQL 8 here's the DBfiddle link with the schema and your solution added https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d7b284091af034cd4aaa1b3d517858af –  Mar 24 '22 at 17:07
  • Can’t wait to try this out tomorrow, superexcited, been working on this for a while now. Thanks to both of u in advance, I really really appreciate it!! – Robin Lindström Mar 24 '22 at 18:08
  • I tried both of them out, but got an error when I tried Stus, don't really understand why ? :) I am using Snowflake ------- message: SQL compilation error: Window function [MIN(CASE(T2.WEEKDAY > T.WEEKDAY, T2.WEEKDAY, null)) OVER ()] contains a correlation. – Robin Lindström Mar 25 '22 at 14:31
  • @RobinLindström - The query is perfectly valid, as you can see [in this demo fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=04976873a23021a4db25a3d9843ad727) which is using MySql - the RDBMS you tagged in your question. If you don't need a solution for MySql, why would you tag it over tagging Snowflake - which you don't even mention anywhere in your question? Disappointing that Snowflake only has a limited implementation of ANSI SQL it seems. – Stu Mar 26 '22 at 21:30
  • 1
    Hi Stu, my apologies that I tagged mysql instead of snowflake, I am very new to sql and thought it didn’t matter, that it works in both but clearly it doesn’t. Again, apologies and next time I will make sure to tag Snowflake. Thanks for taking the time to answer though, thought your solution looked very neat so wanted to try it out! All the best! – Robin Lindström Mar 28 '22 at 15:45