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

Откуда:
Сообщений: 79
Есть следующая таблица:
Tab1 (ObjID int, StartDate datetime, StopDate datetime, DayWork int)
Описывает время работы объекта (часов в день) наблюдаемое в определенный период.

Необходимо собрать суммарное время работы по месячно.
Tab2 (ObjID int, MonthID int, Work int)
Проблема в том что StartDate и StopDate произвольны и могут попадать и на середину месяца.

Т.е. например:
10 2004-01-01 2004-03-15 1
Соответственно нужно получить
10 1 31
10 2 29
10 3 15
(DayWork умножается на количество дней работы зафиксироанных в определенном месяце)

Можно ли это как-то выбрать в запросе, или придется сложный цыкл организовывать.

Спасибо
18 мар 04, 14:08    [585179]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
Дмитрий Валуев
Member

Откуда: Южное Тушино
Сообщений: 232
Как направление для дальнейшего развития
declare @Tab1 table (ObjID int, StartDate datetime, StopDate datetime, DayWork int) 

insert into @tab1
select 10, '2004-01-01', '2004-03-15', 1

select c.mes,datepart(d,cast(c.ye+c.mes1+'01' as datetime)-1) days
from (
select b.mes,cast(a.ye as char(4)) ye,'0'+cast(b.mes+1 as char(1)) mes1 from
(select datediff(m,startdate,stopdate) pm, year(startdate) ye from @tab1) a
inner join
(select 1 mes union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 10
union select 11 union select 12 ) b
on a.pm>=b.mes
)c

Список месяцев между двумя датами получаем соединением разницы дат в месяцах с 1,2,...12 по условию >=. Здесь на самом деле надо аккуратнее написать, у меня правильно только если месяц Startdate январь.
Для того, чтобы получить количество дней в месяце берем 1 число следующего месяца, вычитаем 1 день и берем значение дня.
18 мар 04, 15:02    [585390]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
iSestrin
Member

Откуда: Новосибирск
Сообщений: 3811
нужна табличка или вью - календарик, как ее получить - муссировалось 100 раз, поиск поможет

далее просто объединяем и count(*)*DayWork
18 мар 04, 15:03    [585393]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
iSestrin
Member

Откуда: Новосибирск
Сообщений: 3811
интересная задачка, захотелось решить (calendar у меня в базе есть):

create table #t (id int, d1 datetime, d2 datetime, DayWork int)
insert into #t select 10, '20040101', '20040315', 1

select year(t1.day), month(t1.day), count(*) from (select c.day from #t t, calendar c where t.d1-1<c.day) t1
inner join (select c.day from #t t, calendar c where t.d2+1>c.day) t2
on t1.day=t2.day
group by year(t1.day), month(t1.day)

drop table #t
18 мар 04, 15:12    [585427]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
iSestrin
Member

Откуда: Новосибирск
Сообщений: 3811
ага, еще учел DayWork :

create table #t (id int, d1 datetime, d2 datetime, DayWork int)
insert into #t select 10, '20040101', '20040315', 2

select year(t1.day), month(t1.day), count(*)*DayWork from (select c.day, t.DayWork from #t t, calendar c where t.d1-1<c.day) t1
inner join (select c.day from #t t, calendar c where t.d2+1>c.day) t2
on t1.day=t2.day
group by year(t1.day), month(t1.day), DayWork

drop table #t
18 мар 04, 15:14    [585433]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
SergSuper
Member

Откуда: SPb
Сообщений: 5488
чё-то просто не получается
declare @t table(start datetime, stop datetime)

insert @t select '20040102','20040111'
union select '20040122','20040211'
union select '20040222','20040511'
-----------

declare @m table (m int)
insert @m select 1 union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11 union select 12

select
m,
sum(case when m between month(start) and month(stop) then datediff(dd,dateadd(mm,m-1,'20040101'),dateadd(mm,m,'20040101')) else 0 end -
case when month(start)=m then datediff(dd, dateadd(mm,m-1,'20040101'), start) else 0 end -
case when month(stop)=m then datediff(dd, stop, dateadd(mm,m,'20040101')) else 0 end )

from @t t, @m m
group by m

Возможны ошибки, где-то наверняка лишняя единица прибавляется или отнимается, главное поймите принцип
Суть в следущем - если месяц попадает в период, то берется длинна месяца и если края диапазона попадают в период из общей длинны вычитаются дни от начала либо от конца начала месяца до соответственно начала или конца диапазона
18 мар 04, 15:17    [585444]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
Дмитрий Валуев
Member

Откуда: Южное Тушино
Сообщений: 232
Доработал вариант с календариком от iSestrin, он не учитывал случай когда в одном месяце есть периоды работы с разным daywork. И календарика у меня в базе нет :(
declare @Tab1 table (ObjID int, StartDate datetime, StopDate datetime, DayWork int) 

insert into @tab1
select 10, '2004-01-01', '2004-03-15', 1
union select 10, '2004-03-16', '2004-03-31', 2

select d.objid,d.gg,d.mm,sum(d.cnt) from
(select t.objid,year(c.date) gg, month(c.date) mm,count(*)*t.daywork cnt from
(select top 366 date from(
select convert(datetime, '20040101') + n3.num*100+n2.num*10+n1.num as date from
(select 0 as num union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) n1,
(select 0 as num union select 1 union select 2 union select 3 union select 4
union select 5 union select 6 union select 7 union select 8 union select 9) n2,
(select 0 as num union select 1 union select 2 union select 3)n3
) a
)c inner join @tab1 t on c.date between t.startdate and t.stopdate
group by t.objid,year(c.date), month(c.date),t.daywork
)d
group by d.objid,d.gg,d.mm

Должно работать, так наверное проще всего запрограммировать. Вариант SergSuper и мой первый вариант для реализации сложнее.
18 мар 04, 15:57    [585568]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
Bertic
Member

Откуда:
Сообщений: 51

create table #data
(
ID int,
StartDate datetime,
EndDate datetime,
VHour int
)

INSERT #data ( ID, StartDate, EndDate, VHour )
VALUES( 10, '20040101', '20040315', 1 )

INSERT #data ( ID, StartDate, EndDate, VHour )
VALUES( 11, '20040207', '20040310', 5 )

create table #t
( MonthNum int )

INSERT #t ( MonthNum )
SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12

DECLARE
@FirstDayYear datetime
SET @FirstDayYear = CAST( Year( GETDATE() ) as varchar ) + '0101'

select ID,
case MonthNum-month( EndDate )
when 0 then DateDiff( dd, DateAdd( mm, MonthNum-1, @FirstDayYear ), EndDate )+1
else
case MonthNum-month( StartDate )
when 0 then DateDiff( dd, StartDate, DateAdd( mm, MonthNum, @FirstDayYear ) )
else DateDiff( dd, DateAdd( mm, MonthNum-1, @FirstDayYear ), DateAdd( mm, MonthNum, @FirstDayYear ) )
end
end * VHour
from #data, #t
where
MonthNum Between MONTH( StartDate ) and MONTH( EndDate )
Order by id, MonthNum

drop table #t
drop table #data


Результат:

10 31
10 29
10 15
11 115
11 50
18 мар 04, 16:31    [585674]     Ответить | Цитировать Сообщить модератору
 Re: Сложная (для меня) выборка прошу помощи  [new]
Bertic
Member

Откуда:
Сообщений: 51
Да еще MonthNum добавить в запрос.
18 мар 04, 16:37    [585696]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить