Lesson 6 of 10 · Lab

Filter rows by date and account for what's left out

Filter keeps the rows that pass a rule and sends the rest to a second output. Kept plus rejected rows must equal the rows that came in, and each rejected row needs a reason.

A rule that combines status = 'completed' with a date range keeps finished hires inside it. A row whose date is empty fails it, because an unknown date isn't between anything.

Marit, who owns fictional Kramerica Bikes, wants Harbour's week of September 14 to 20, finished hires only. Tomas's 321 rows arrive as the last lesson left them, with hire_on a date and amount_eur a number.

Three hires are dated see note in the till, so their hire_on is empty. Will the week's rule keep them?

No, they go to False. They're real hires, so the report must account for them.

Filter the week, then read what fell out

  1. This live flow in Strata, Oplema's browser app for data prep, opens with Filter selected. Its Expression keeps finished hires from September 1 to 30. OUTPUT TRUE opens first.
  2. Select Summarize, the upper block on the right. It adds up the kept rows: 283 hires and a gross of 11293.5 (€11,293.50), the price of finished hires, VAT included.
  3. Select Filter. In Expression, double-click 01 in 2026-09-01 and type 14. Change 30 in 2026-09-30 to 20, then select the Filter block.
  4. Select Summarize again. It reads 93 hires and a gross of 4093.5. priced counts the 91 hires that have an amount.
  5. Drag from Filter's lower right dot, its False output, to Sort's left dot.
  6. Select Sort. Set Column to hire_on and Direction to Descending.
  7. Read Sort's preview of 228 rows and scroll right to status. The first 13 are the week's cancelled and no-show bookings, then September 13 back to 1.
  8. Scroll to the bottom. The last three rows have no hire_on, and their hire_date says see note.

Swipe sideways to see the canvas, or open this page on a larger screen to work with it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the two outputs hold

True keeps 93 hires with a gross of €4,093.50. priced says 91 because two staff hires are free. False holds 228 rows: 212 from other weeks, the week's 11 cancellations and 2 no-shows, then 3 hires with no date, worth €93.00 gross. 93 + 228 = 321.

Every rejected row needs a reason

Cancellations and no-shows are out by your rule, as Marit asked. The three undated hires are different: they happened, but no date rule can place them. Report them beside the week's figure: 93 hires, and 3 more with no date, worth €93.00 gross. Next, take VAT off the 93.

See the Filter and Sort references. For later: Filtering rows explains why an empty value fails every ordinary comparison.

Common date filter mistakes

  • Stopping the range a day early. BETWEEN includes both dates, while hire_on < DATE '2026-09-20' drops the Sunday.
  • Comparing dates written as text. As text, '01/10/2026' comes before '14/09/2026', so a range on text dates mixes up months.
  • Leaving the False output unread. Nothing warns you when real rows fall out, so read it or count it.

Date ranges in SQL and Excel

SELECT * FROM harbour
WHERE status = 'completed'
  AND hire_on BETWEEN DATE '2026-09-14' AND DATE '2026-09-20';

SELECT * FROM harbour
WHERE NOT (status = 'completed'
  AND hire_on BETWEEN DATE '2026-09-14' AND DATE '2026-09-20')
   OR hire_on IS NULL;

SQL has no False output, so the second query rebuilds it. IS NULL is needed because NOT of an unknown is still unknown, so the undated hires would vanish from both lists. In Excel, =FILTER(A2:G322, (G2:G322="completed")*(C2:C322>=DATE(2026,9,14))*(C2:C322<=DATE(2026,9,20))) returns the kept rows; the rejected ones need a second formula.