вторник, и необходимо найти следующую пятницу. При использовании функции TO CHAR с форматной маской d или функции DAYOFWEEK получаем 6 для пятницы и 3 для вторника. Чтобы получить 6 из 3, просто находим их разность (6 - 3 = 3) и прибавляем ее к меньшему значению ((6 - 3) + 3 = 6). Итак, независимо от реальных дат, если числовое значение дня, с которого начинается отсчет, меньше, чем числовое значение искомого дня, добавление разности между этими двумя днями к начальной дате даст искомую дату.

Если выражение SIGN дает в результате 1, первый день месяца выпадает на день между вторником и субботой (включительно). Если числовое значение первого дня месяца больше 2 (понедельник), вычтете из 7 разность между числовым значением первого дня месяца и числовым значением понедельника (2) и затем прибавьте полученное значение к первому дню месяца. Таким образом, получаем искомый день недели, в данном случае понедельник.

Опять же, если есть затруднения с пониманием логики операций, забудьте о названиях дней недели и просто оперируйте числами. Например, предположим, требуется найти следующий вторник, начиная с пятницы. Числовое значение вторника (3) меньше, чем значение пятницы (6). Чтобы попасть на третий день с шестого, вычитаем из 7 разность между этими двумя значениями (7 - ( 3 - 6 ) = 4) и добавляем результат (4) к начальному дню (пятнице). (Применение вертикальных черточек в выражении 3 - 6 обеспечивает получение абсолютного значения разности.) Здесь мы не добавляем 4 к 6 (что в результате дало бы 10), мы добавляем четыре дня к пятнице, что обеспечит возвращение следующего вторника.

Идея, стоящая за применением выражения CASE, заключается в создании своего рода функции следующий день для PostgreSQL и MySQL. Если вычисления начинаются не с первого дня месяца, в столбце DY будет располагаться значение, возвращенное функцией CURRENT DATE. Выражение CASE в этом случае возвратит дату следующего после текущей даты понедельника (если сама текущая дата соответствует понедельнику, будет возвращена она).

Получив первый понедельник месяца, добавляем 21 или 28 дней, чтобы найти последний понедельник. Сколько дней необходимо добавить (21 или 28), определяет выражение CASE (строки 2-5). Оно добавляет к текущей дате 28 дней и проверяет, приводит ли это к переходу в следующий месяц. Выражение CASE осуществляет это следующим образом:

1. К значению, возвращенному функцией FIRST MONDAY, добавляется 28.

2. С помощью функции TO CHAR (PostgreSQL) или MONTH из результата выражения FIRST MONDAY + 28 извлекается название получаемого месяца.

3. Результат шага 2 сравнивается со значением MTH из вложенного запроса. Значение MTH - это название текущего месяца, полученное в результате выполнения функции CURRENT DATE. Если значения совпадают, следовательно, текущий месяц длинный и необходимо добавлять 28 дней; выражение CASE возвращает FIRST MONDAY + 28. Если значения месяцев не совпадают, значит, при добавлении 28 дней мы выходим за рамки одного месяца, и выражение CASE возвращает FIRST MONDAY + 21. Длительность наших месяцев такова, что проверять приходится только два возможных значения, 28 и 21. Это очень удобно.

Можно расширить решение, добавляя 7 и 14 дней, для поиска второго и третьего понедельников месяца соответственно.

Создание календаря Задача

Требуется создать календарь на текущий месяц. Он должен быть отформатирован, как обычный календарь: семь столбцов в ширину и (как правило) пять строк вниз.

Решение

Решения будут выглядеть немного по-разному, но все они решают эту задачу одинаково: возвращают все дни текущего месяца и затем разделяют их на недели по одному из дней недели, создавая календарь.

Существуют разные форматы календарей. Например, команда cal UNIX форматирует неделю от воскресенья до субботы. В примерах данного рецепта используется стандартная нумерация недель ISO, поэтому удобнее всего будет создавать неделю, начиная с понедельника. Полностью разобравшись с решениями, вы поймете, что изменение формата календаря - это просто вопрос корректировки значений, определяемых ISO-номерами недель.

Как только мы начинаем использовать в SQL разные типы форматирования для создания удобных для чтения результатов, запросы становятся длиннее. Не позволяйте этим длинным запросам запутать вас. Представленные в данном рецепте запросы окажутся предельно простыми при разложении их на составляющие и выполнении одного за другим.

Чтобы возвратить все дни текущего месяца, используйте рекурсивный оператор WITH. Затем разбейте месяц на недели по выбранному дню с помощью выражений CASE и функций MAX:

1 with x(dy,dm,mth,dw,wk)

2 as (

3 select (current date -day(current date) day +1 day) dy,

4 day((current date -day(current date) day +1 day)) dm,

5 month(current date) mth,

6 dayofweek(current date -day(current date) day +1 day) dw,

7 week iso(current date -day(current date) day +1 day) wk

8 from t1

9 union all

10 select dy+1 day, day(dy+1 day), mth,

11 dayofweek(dy+1 day), week iso(dy+1 day)

12 from x

13 where month(dy+1 day) = mth

14 )

15 select max(case dw when 2 then dm end) as Mo,

16 max(case dw when 3 then dm end) as Tu,

17 max(case dw when 4 then dm end) as We,

18 max(case dw when 5 then dm end) as Th,

19 max(case dw when 6 then dm end) as Fr,

20 max(case dw when 7 then dm end) as Sa,

21 max(case dw when 1 then dm end) as Su

22 from x

23 group by wk

24 order by wk

Oracle

Чтобы возвратить все дни текущего месяца, используйте рекурсивный оператор CONNECT BY. Затем разбейте месяц на недели по выбранному дню с помощью выражений CASE и функций MAX:

1 with x

2 as (

3 select *

4 from (

5 select to char(trunc(sysdate,mm)+level-1,iw) wk,

6 to char(trunc(sysdate,mm)+level-1,dd) dm,

7 to number(to char(trunc(sysdate,mm)+level-1,d)) dw,

8 to char(trunc(sysdate,mm)+level-1,mm) curr mth,

9 to char(sysdate,mm) mth

10 from dual

11 connect by level <= 31

12 )

13 where curr mth = mth

14 ) 15 16 17 18 19 20 21