Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите с запросом с датами и case ' ом.  [new]
SS13SS13
Member

Откуда:
Сообщений: 34
Имеется 3 таблицы
car (машины)
id model
1 Камаз
2 Газель

work (даты проведения работ этими машинами)
id date_start date_end
1 01.01.2012 03.01.2012
2 02.01.2012 03.01.2012
1 07.01.2012 09.01.2012

repair (ремонт, когда машина стояла на ремонте)
id date_start date_finish
1 10.01.2012 11.01.2012

Рассмотрим самый простой случай с датами без времени, когда в один и тот же промежуток времени (дат) одна и та же машина не может и работать и стоять на ремонте.
На выходе необходимо получить отчет по всем дням начиная с меньшей даты в одной из таблиц work или repair по максимальную дату из этих же таблиц по всем машинам парка, при этом обозначить, что в этот день делала машина работа/ремонт/простой
Результат должен быть таким:
report_date id model type
01.01.2012 1 Камаз Работа
01.01.2012 2 Газель Простой
02.01.2012 1 Камаз Работа
02.01.2012 2 Газель Работа
03.01.2012 1 Камаз Работа
03.01.2012 2 Газель Работа
04.01.2012 1 Камаз Простой
04.01.2012 2 Газель Простой
05.01.2012 1 Камаз Простой
05.01.2012 2 Газель Простой
06.01.2012 1 Камаз Простой
06.01.2012 2 Газель Простой
07.01.2012 1 Камаз Работа
07.01.2012 2 Газель Простой
08.01.2012 1 Камаз Работа
08.01.2012 2 Газель Простой
09.01.2012 1 Камаз Работа
09.01.2012 2 Газель Простой
10.01.2012 1 Камаз Ремонт
10.01.2012 2 Газель Простой
11.01.2012 1 Камаз Ремонт
11.01.2012 2 Газель Простой


Для этого я сделал запрос, но никак не могу его доработать (ошибка в том что даты не берутся из таблицы с ремонтом и как организовать правильно case выражение)

select dateadd(dd, sv.rn, w.date_start) as report_date, c.id, c.model,
(CASE WHEN w.date_start='' THEN 'Работа'
WHEN r.date_start='' THEN 'Ремонт'
ELSE 'Простой' END) AS type
from work w
join (select row_number() over (order by number) - 1 rn from master.dbo.spt_values) sv on sv.rn <= datediff(dd, w.date_start, w.date_end),
car c, repair r order by report_date
11 июл 13, 19:30    [14554633]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
set dateformat dmy
declare @date_from date='01.01.2011', @date_to date='10.01.2012'
/*;with 
car as (select * from (values
	(1,'Камаз'),(2,'Газель')
	)temp_car(id,model)),
work as (select * from (values
   (1,convert(date,'04.01.2012'),convert(date,'05.01.2012')),
   (2,'06.01.2012','08.01.2012'), (1,'07.01.2012','09.01.2012')
	)temp_work(id,date_start,date_end)),
remont as (select * from (values
   (1,convert(date,'10.01.2012'),convert(date,'13.01.2012'))
	)temp_remont(id,date_start,date_finish))*/
select d.day, d.id, d.model, ISNULL(w.typ,'Простой')typ
from (
   select day=DATEADD(DAY,v.number,a.date_start), c.id, c.model
      from master..spt_values v 
      cross join car c
      cross join (
         select convert(date,MAX(date_start))date_start, convert(date,MIN(date_end))date_end
         from (
            select MIN(date_start)date_start, MAX(date_end)date_end 
            from (
               select MIN(date_start)date_start, MAX(date_end)date_end from work 
               union all
               select MIN(date_start), MAX(date_finish) from remont 
               ) u
            union all
            select @date_from, @date_to
            )u
         )a
      where v.type='P' and v.number<=DATEDIFF(DAY, a.date_start, a.date_end)
   ) d
left join (
   select day=DATEADD(DAY,v.number,w.date_start), '+Работа' typ, w.id
      from work w
      join master..spt_values v on v.type='P' and v.number<=DATEDIFF(DAY, w.date_start, w.date_end)
   union all
   select day=DATEADD(DAY,v.number,r.date_start), '\Ремонт' typ, r.id
      from remont r
      join master..spt_values v on v.type='P' and v.number<=DATEDIFF(DAY, r.date_start, r.date_finish)
   )w on w.id=d.id and w.day=d.day
order by d.id, d.day
+ итог
dayidmodeltyp
2012-01-041Камаз+Работа
2012-01-051Камаз+Работа
2012-01-061КамазПростой
2012-01-071Камаз+Работа
2012-01-081Камаз+Работа
2012-01-091Камаз+Работа
2012-01-101Камаз\Ремонт
2012-01-042ГазельПростой
2012-01-052ГазельПростой
2012-01-062Газель+Работа
2012-01-072Газель+Работа
2012-01-082Газель+Работа
2012-01-092ГазельПростой
2012-01-102ГазельПростой
11 июл 13, 21:21    [14554943]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
SS13SS13
Member

Откуда:
Сообщений: 34
Cygapb-007, Вы мой спаситель, спасибо огромное!
11 июл 13, 21:49    [14555036]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
SS13SS13
Member

Откуда:
Сообщений: 34
Cygapb-007, и еще вопрос зачем плюс и черта рядом в выводом Работа и Простой ? И почемуу без них отображается Просто
11 июл 13, 22:01    [14555081]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
SS13SS13
почемуу без них отображается Просто
convert(char(6),'Простой')='Просто'
12 июл 13, 10:56    [14556208]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Cygapb-007,

Если не трудно, поясните пожалуйста логику.
Я пытаюсь разобрать код, но погода плохая, голова болит и не соображает, а задачка и решение интересные!
15 июл 13, 17:18    [14569296]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
from (...) d- отбирает минимально необходимый диапазон дат и для каждой машины из car разворачивает его в календарь

left join(...) w - диапазон для каждой строки из work разворачивается в календарь для машины из этой строки, результат объединяется с аналогичным календарем из remont, полученный результат накладывается на сформированный ранее календарь.

Перекрывания диапазонов не может быть по условию, а дни, в которые не было ни ремонта, ни работы (то есть w.typ is null) - дни простоя
15 июл 13, 17:48    [14569518]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Cygapb-007
from (...) d- отбирает минимально необходимый диапазон дат и для каждой машины из car разворачивает его в календарь


Вот можно чуть подробнее про разворачивание в календарь.
15 июл 13, 18:21    [14569710]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Eugene_p1
Вот можно чуть подробнее про разворачивание в календарь
declare @dtf datetime='20130101', @dtt datetime='20130103';
select car.*, v.number, dateadd(day,v.number,@dtf)day
from master..sptvalues v -- подробнее про генерацию числовой последовательности - в поиске 
cross join (select 1,'car1' union all select 2,'car2')car(id,name)
where v.type='P' and v.number<=datediff(day,@dtf,@dtt)-- от 0 до числа дней между двумя датами
15 июл 13, 19:16    [14569894]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом с датами и case ' ом.  [new]
Eugene_p1
Member

Откуда: Москва
Сообщений: 295
Cygapb-007,

Спасибо!
16 июл 13, 15:24    [14573765]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить