W Cat - Справка по SQL

SELECT DATEDIFF(dd, (SELECT MIN(date) FROM pass_in_trip), (SELECT MAX(date) FROM pass_in_trip))

Пример (схема 4). Определить продолжительность рейса 1123 в минутах.

Здесь следует принять во внимание, что время вылета ( time_out ) и время прилета ( time_in ) хранится в полях типа datetimeтаблицы Trip. Заметим, что SQL Server вплоть до версии 2000 не имеет отдельных темпоральных типов данных для даты и времени, появление которых ожидается в следующей версии (Yukon). Поэтому при вставке в поле datetimeтолько времени (например, UPDATE trip SET time_out = '17:24:00' WHERE trip_no=1123), время будет дополнено значением даты по умолчанию ('1900-01-01').

Напрашивающееся решение

SELECT DATEDIFF(mi, time_out, time_in) dur FROM trip WHERE trip_no=1123,

(которое дает -760) будет неверным по двум причинам.

Во-первых, для рейсов, которые вылетают в один день, а прилетают на следующий, вычисленное таким способом значение будет неправильным. Во-вторых, ненадежно делать какие либо предположения относительно дня, который присутствует только в силу необходимости соответствовать типу datetime.

Но как определить, что самолет приземлился на следующий день? Тут помогает описание предметной области, где говорится, что полет не может продолжаться более суток. Итак, если время прилета не больше, чем время вылета, то этот факт имеет место. Теперь второй вопрос: как посчитать только время, с каким бы днем оно ни стояло?

Здесь может помочь функция T-SQL DATEPART.

Синтаксис

DATEPART ( datepart , date )

Эта функция возвращает целое число, представляющее собой указанную аргументом datepart часть заданной вторым аргументом даты ( date ).

Список допустимых значений аргумента datepart , описанный выше в данном разделе, дополняется еще одним значением

Заметим, что возвращаемое функцией DATEPARTзначение в этом случае (номер дня недели) зависит от настроек, которые можно изменить с помощью оператора SET DATEFIRST, устанавливающего первый день недели. Для кого-то понедельник - день тяжелый, а для кого-то - воскресенье. Кстати, последнее значение принимается по умолчанию.

Однако вернемся к нашему примеру. В предположении, что время вылета/прилета является кратным минуте, мы можем его определить как сумму часов и минут. Поскольку функции даты/времени работают с целочисленными значениями, приведем результат к наименьшему интервалу - минутам. Итак, время вылета рейса 1123 в минутах

SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) FROM trip WHERE trip_no=1123

и время прилета

SELECT DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) FROM trip WHERE trip_no=1123

Теперь мы должны сравнить, превышает ли время прилета время вылета. Если это так, вычесть из первого второе, чтобы получить продолжительность рейса. В противном случае к разности нужно добавить одни сутки (24*60 = 1440 минут).

SELECT CASE WHEN time_dep=time_arr THEN time_arr-time_dep+1440 ELSE time_arr-time_dep END dur FROM

( SELECT DATEPART(hh, time_out)*60 + DATEPART(mi, time_out) time_dep, DATEPART(hh, time_in)*60 + DATEPART(mi, time_in) time_arr FROM trip WHERE trip_no=1123) tm

Здесь, чтобы не повторять длинные конструкции в операторе CASE, использован подзапрос. Конечно, результат получился достаточно громоздким, зато абсолютно корректным в свете сделанных к этой задаче замечаний.

Пример (4 схема). Определить дату и время вылета рейса 1123.

В таблице совершенных рейсов Pass_in_trip содержится только дата рейса, но не время, т.к. в соответствии с предметной областью каждый рейс может выполняться только один раз в день. Для решения этой задачи нужно к дате, хранящейся в таблице Pass_in_trip, добавить время из таблицы Trip

SELECT pt.trip_no, DATEADD(mi, DATEPART(hh,time_out)*60 + DATEPART(mi,time_out), date) [time]

FROM pass_in_trip pt JOIN trip t ON pt.trip_no=t.trip_no WHERE t.trip_no=1123

Выполнив запрос, получим следующий результат

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

Синтаксис

DATENAME ( datepart , date )

Эта функция возвращает символьное представление составляющей ( datepart ) указанной даты ( date ). Аргумент, определяющий составляющую даты, может принимать одно из значений, перечисленных в вышеприведенной таблице.