The goal was to find a way to always select only Monday through Friday of either the previous week or the current week as the reporting period. Starting Friday (night), Saturday, and Sunday, the current week should always be selected; otherwise, the previous week should be selected.

Example: At the end of a week, a bank should select only the transactions, or a factory should select only the production processes, from the previous week or, otherwise, the week before that.


-- 1 CURRENT_WEEKDAY MONDAY FRIDAY
-- ---------- --------------- ---------- ----------
-- 2017-04-01 7 2017-03-27 2017-03-31
SELECT DATE('1.4.2017'),
DAYOFWEEK( DATE('1.4.2017') ) AS CURRENT_WEEKDAY,
CASE
WHEN DAYOFWEEK( DATE('1.4.2017') ) < 6 THEN DATE('1.4.2017') - (5 + DAYOFWEEK( DATE('1.4.2017') ) ) DAYS ELSE DATE('1.4.2017') - (DAYOFWEEK(DATE('1.4.2017') ) -2 ) DAYS END AS MONDAY, CASE WHEN DAYOFWEEK( DATE('1.4.2017') ) < 6 THEN DATE('1.4.2017') - (1 + DAYOFWEEK( DATE('1.4.2017') ) ) DAYS ELSE DATE('1.4.2017') + (6 - DAYOFWEEK( DATE('1.4.2017')) ) DAYS END AS FRIDAY FROM SYSIBM.SYSDUMMY1; -- 1 CURRENT_WEEKDAY MONDAY FRIDAY -- ---------- --------------- ---------- ---------- -- 2017-04-07 6 2017-04-03 2017-04-07 SELECT DATE('7.4.2017'), DAYOFWEEK( DATE('7.4.2017') ) AS CURRENT_WEEKDAY, CASE WHEN DAYOFWEEK( DATE('7.4.2017') ) < 6 THEN DATE('7.4.2017') - (5 + DAYOFWEEK( DATE('7.4.2017') ) ) DAYS ELSE DATE('7.4.2017') - (DAYOFWEEK(DATE('7.4.2017') ) -2 ) DAYS END AS MONDAY, CASE WHEN DAYOFWEEK( DATE('7.4.2017') ) < 6 THEN DATE('7.4.2017') - (1 + DAYOFWEEK( DATE('7.4.2017') ) ) DAYS ELSE DATE('7.4.2017') + (6 - DAYOFWEEK( DATE('7.4.2017')) ) DAYS END AS FRIDAY FROM SYSIBM.SYSDUMMY1;

On Friday, Saturday, and Sunday, the period from Monday to Friday of the same week is determined; otherwise, it is always the period from the previous week.

Instead of a static date, the company’s posting date, production date, or value date from a central table—or the current date (CURRENT DATE)—can, of course, be used. And viewed in isolation, the SELECT statement makes little sense; rather, it must be combined with additional business logic, e.g., in an upstream WITH statement combined with the desired logic for selecting the company data.

Further suggestions for solving such and similar SQL problems are welcome!