Преобразование значений null. Программирование

Программирование >> Преобразование значений null

1 ... 103 104 105 [ 106 ] 107 108 109 ... 219

select cast(substring(cast(yrq as varchar),1,4)+-+

cast(yrq%10*3 as varchar)+-1 as datetime) q end from (

select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1 union all select 20054 yrq from t1 ) x

Q END

01-MAR-2005 01-JUN-2005 01-SEP-2005 01-DEC-2005

Значения столбца Q END - первый день последнего месяца каждого квартала. Чтобы получить последний день месяца, прибавляем один месяц к значению Q END и вычитаем один день, используя функцию DATEADD. Чтобы найти дату начала каждого квартала, вычитаем два месяца из Q END, используя функцию DATEADD.

Дополнение отсутствующих дат Задача

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

select distinct

extract(year from hiredate) as year from emp

YEAR

1980 1981 1982 1983

Требуется определить, сколько сотрудников принималось на работу каждый месяц в период с 1980 по 1983 г. Ниже частично представлено результирующее множество, которое должно быть получено:

MTH NUM HIRED

01-JAN-1981

01-FEB-1981 01-MAR-1981 01-APR-1981 01-MAY-1981 01-JUN-1981 01-JUL-1981 01-AUG-1981 01-SEP-1981 01-OCT-1981 01-NOV-1981

01-DEC-1981

Решение

Тонкость в том, что требуется возвратить строки для всех месяцев, даже для тех, в которые не было принято на работу ни одного служащего (т. е. количество принятых на работу равно нулю). Поэтому строки для тех месяцев, в которые служащие не принимались, необходимо сгенерировать самостоятельно и затем провести их внешнее объединение с таблицей EMP по столбцу HIREDATE (выбирая из фактических значений HIREDATE только месяц, чтобы их можно было сопоставлять со сгенерированными).

Чтобы получить все месяцы (первый день каждого месяца с 1 января 1980 по 1 декабря 1983 г.), используйте рекурсивный оператор WITH. Имея все месяцы заданного диапазона дат, проведите внешнее объединение с таблицей EMP и используйте агрегатную функцию COUNT, чтобы подсчитать, сколько сотрудников было принято на работу в каждом месяце:

	with	x (start date,end date)

	select	(min(hiredate) -
		dayofyear(min(hiredate)) day +1 day) start date,
		(max(hiredate) -
		dayofyear(max(hiredate)) day +1 day) +1 year end date
	from
	union
	select	start date +1 month, end date
	from
	where	(start date +1 month) < end date

	select	x.start date mth, count(e.hiredate) num hired
	from	x left join emp e
		(x.start date = (e.hiredate-(day(hiredate)-1) day))
	group	by x.start date
	order	by 1

Oracle

Чтобы получить все месяцы с 1980 по 1983 год, используйте оператор CONNECT BY. Затем проведите внешнее объединение с таблицей EMP и используйте агрегатную функцию COUNT, чтобы подсчитать, сколько сотрудников было принято на работу в каждом месяце. Для Oracle 8i Database и более ранних версий ANSI-синтаксис внешнего объединения недоступен, также как и применение оператора CONNECT BY для формирования строк. Простой выход в таком случае - традиционная сводная таблица (как в решении для MySQL). Ниже приведено решение для Oracle с использованием синтаксиса Oracle для внешнего объединения:

1 with x

2 as (

3 select add months(start date,level-1) start date

4 from (

5 select min(trunc(hiredate,y)) start date,

b add months(max(trunc(hiredate,y)),12) end date

7 from emp

9 connect by level <= months between(end date,start date)

10 )

11 select x.start date MTH, count(e.hiredate) num hired

12 from x, emp e

13 where x.start date = trunc(e.hiredate( + ),mm)

14 group by x.start date

15 order by 1

а далее - второе решение для Oracle, на этот раз с использованием синтаксиса, соответствующего стандарту ANSI: