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

Откуда: урал
Сообщений: 2852
Имеется набор рабочих часов офиса, в виде интервалов и дней недели, в которые эти интервалы применяются. Надо получить разницу между двумя датами, но без учета нерабочих часов, т.е. то время, которое попадает в нерабочий интервал учитывать не надо. Как к такому подойти?


--Business hours	Monday – Wednesday	8:30	19:30
--After hours	Monday – Wednesday	19:31	8:29
--Business hours	Thursday - Friday	8:30	21:00
--After hours	Thursday - Friday	21:01	8:59
--Business hours	Saturday - Sunday	9:00	19:00
--After hours	Saturday - Sunday	19:01	8:29

set datefirst 1

declare @intervals table(ID int, Start time, Finish time, WeekDayStart tinyint, WeekDayFinish tinyint, IsWorking bit)


-- business hours
insert into @intervals values(1, '8:30', '8:59:59', 1, 5, 1)
insert into @intervals values(2, '9:00', '19:00:59', 1, 7, 1)
insert into @intervals values(5, '19:01', '19:30:59', 1, 5, 1)
insert into @intervals values(6, '19:30', '21:00:59', 4, 5, 1)

-- outside business hours
insert into @intervals values(9, '19:01', '23:59:59', 6, 7, 0)
insert into @intervals values(9, '19:31', '23:59:59', 1, 3, 0)
insert into @intervals values(9, '21:01', '23:59:59', 4, 5, 0)
insert into @intervals values(9, '00:00', '08:29:59', 1, 5, 0)
insert into @intervals values(9, '00:00', '08:59:59', 6, 7, 0);

select datediff(n, '09/15/2013 12:35:44', '09/22/2013 15:45:22')
20 сен 13, 08:29    [14862247]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
Для полных суток рассчитать таблицу "нерабочих часов" для дней недели и тупо вычесть.
Для неполных суток (их всего то двое ) - искать пересечения с рабочими али нерабочими интервалами.

ЗЫ. Ну нафега хранить рабочие и нерабочие интервалы? Чо, ишо какие-то есть?
20 сен 13, 08:49    [14862276]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
stenford
Member

Откуда: урал
Сообщений: 2852
эти сутки как-то надо получить сначала, как это вообще сделать лучше, ведь рабочие часы для разных дней разные, скажем между 10 сентября и 20 сентября, неужели курсором создавать таблицу дней и приклеивать туда интервалы?
23 сен 13, 03:36    [14870534]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
stenford
эти сутки как-то надо получить сначала, как это вообще сделать лучше, ведь рабочие часы для разных дней разные, скажем между 10 сентября и 20 сентября, неужели курсором создавать таблицу дней и приклеивать туда интервалы?


Господи! За что ты наказал этого человека?!
Ну рассчитай для еще и для недели. Тогда "приклеивать" надо будет меньше.

ЗЫ. Кнопки "Сделать как я хачу" пока не разработали.
23 сен 13, 06:26    [14870556]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
stenford
Member

Откуда: урал
Сообщений: 2852
aleks2
Господи! За что ты наказал этого человека?!
Ну рассчитай для еще и для недели. Тогда "приклеивать" надо будет меньше.

ЗЫ. Кнопки "Сделать как я хачу" пока не разработали.

блин, ты задание не прочитал, или просто сам не знаешь как это делать? Каждый день, в зависимости от дня недели имеет разное время начала/окончания рабочего дня. Например в четверг работают до 21 часа, а в воскресенье до 7. Т.е. надо каким-то образом получить сначала ряд дат, потом в зависимости от того какой это день недели "приклеить" туда интервалы
23 сен 13, 07:16    [14870579]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
stenford
aleks2
Господи! За что ты наказал этого человека?!
Ну рассчитай для еще и для недели. Тогда "приклеивать" надо будет меньше.

ЗЫ. Кнопки "Сделать как я хачу" пока не разработали.

блин, ты задание не прочитал, или просто сам не знаешь как это делать? Каждый день, в зависимости от дня недели имеет разное время начала/окончания рабочего дня. Например в четверг работают до 21 часа, а в воскресенье до 7. Т.е. надо каким-то образом получить сначала ряд дат, потом в зависимости от того какой это день недели "приклеить" туда интервалы


Дарагуля,
1. Это твое задание.
2. Никто его за тебя выполнять не будет.
3. Тебе предложили вариант, но ты волен париться своим веником.
23 сен 13, 10:05    [14870942]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
stenford
Member

Откуда: урал
Сообщений: 2852
aleks2
Дарагуля,
1. Это твое задание.
2. Никто его за тебя выполнять не будет.
3. Тебе предложили вариант, но ты волен париться своим веником.

не знаешь как делать - просьба не засорять топик
23 сен 13, 10:09    [14870961]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2428
stenford,

вам дали подсказку, причем правильную, воспользуйтесь ею, а не требуйте готового кода на блюдечке
23 сен 13, 10:18    [14871000]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
Дарагуля, чего ты выступаешь?
Модераторы не восхитятся... такой ленностью ума.

Ведь тупое ж задание
declare @d1 datetime = '20130101 13:45:00'
      , @d2 datetime = '20130512 11:05:00'
      
declare @firstday datetime, @lastday datetime,
      , @weekcount int
      
      
set @weekcount = datediff(day, cast(@d1 as dateadd)+1, d2)/7;

set @firstday = @d1;
set @lastday = @d2;
set @d1 = dateadd(day, @weekcount*7, cast(@d1 as dateadd)+1);
set @d2 = cast(@d2 as dateadd)-1;

-- итого надо обработать
--1. @firstday
--2. @weekcount
--3. интервал @d1 .. @d2, состоящий из НЕ более 6 ПОЛНЫХ дней. 
--4. @lastday
23 сен 13, 10:20    [14871011]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
stenford
Member

Откуда: урал
Сообщений: 2852
StarikNavy
stenford,

вам дали подсказку, причем правильную, воспользуйтесь ею, а не требуйте готового кода на блюдечке

подсказка в виде "рассчитать таблицу "нерабочих часов" для дней недели" это не подсказка, а перефразировка моего вопроса, мой вопрос в этом и заключался: как я могу расчитать эти часы для дней в зависимости от дня недели
23 сен 13, 10:39    [14871112]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
stenford
Member

Откуда: урал
Сообщений: 2852
aleks2
Дарагуля, чего ты выступаешь?
Модераторы не восхитятся... такой ленностью ума.

Ведь тупое ж задание
declare @d1 datetime = '20130101 13:45:00'
      , @d2 datetime = '20130512 11:05:00'
      
declare @firstday datetime, @lastday datetime,
      , @weekcount int
      
      
set @weekcount = datediff(day, cast(@d1 as dateadd)+1, d2)/7;

set @firstday = @d1;
set @lastday = @d2;
set @d1 = dateadd(day, @weekcount*7, cast(@d1 as dateadd)+1);
set @d2 = cast(@d2 as dateadd)-1;

-- итого надо обработать
--1. @firstday
--2. @weekcount
--3. интервал @d1 .. @d2, состоящий из НЕ более 6 ПОЛНЫХ дней. 
--4. @lastday


ну и где тут учет какие именно дно недели учавствуют-то? Ты-ж просто расчитал число полных дней, а надо - в зависимости от того какой именно день недели каждый из дней, т.к. рабочие часы в пятницу и субботу - разные
23 сен 13, 10:42    [14871126]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
glavryba
Member

Откуда:
Сообщений: 18
Как-то так?

set dateformat ymd;
set datefirst 1;
set nocount on;

-- рабочие часы. Зачем хранить ещё и нерабочие, не очень понятно.
declare @intervals table(
      id int, Start time, Finish time
    , WeekDayStart tinyint, WeekDayFinish tinyint
    , IsWorking bit);
insert into @intervals values(1, '8:30', '8:59:59', 1, 5, 1);
insert into @intervals values(2, '9:00', '19:00:59', 1, 7, 1);
insert into @intervals values(3, '19:01', '19:30:59', 1, 5, 1);
insert into @intervals values(4, '19:30', '21:00:59', 4, 5, 1);

-- время с/по
declare @startingDT datetime = '2013-09-01 15:30:00.000'
      , @endingDT datetime = '2013-09-10 11:00:00.000'
      ;
-- даты с/по (для удобства)
declare @startingDate datetime = dateadd (dd, datediff (dd, 0, @startingDT), 0)
      , @endingDate datetime = dateadd (dd, datediff (dd, 0, @endingDT), 0)
      ;

-- таблица с достаточно длинной последовательностью чисел (1, 2, 3, ..., N)
declare @num table (n int);
insert into @num select row_number() over (order by t.id) from sysobjects t;

-- раскрываем рабочие часы по каждому дню недели
declare @workHours table (weekdayNum int, startingTime time, endingTime time);
insert into @workHours 
    select n.n as WeekDayNum, i.Start, i.Finish
    from @intervals i
    join @num n on (n.n between i.WeekDayStart and i.WeekDayFinish)
    ;

-- собственно, результат
select tt.dt, tt.WeekdayNum, tt.st, tt.et, tt.startingTime, tt.endingTime
from (
    select nn.dt, nn.WeekdayNum, wh.startingTime as st, wh.endingTime as et
          -- приводим в порядок границы интервала слева
        , case when nn.dt + wh.startingTime < @startingDT 
            then @startingDT 
            else nn.dt + wh.startingTime 
          end as startingTime
          -- ... и справа
        , case when nn.dt + wh.endingTime > @endingDT 
            then @endingDT 
            else nn.dt + wh.endingTime 
          end as endingTime
    from (
        -- каждый день из интервала @startingDT .. @endingDT
        select dateadd (dd, n.n - 1, @startingDate) as dt
         , datepart (weekday, dateadd (dd, n.n - 1, @startingDate)) as WeekdayNum
        from @num n
        where n.n <= datediff (dd, @startingDate, @endingDate) + 1
        ) nn
    join @workHours wh on (nn.weekdayNum = wh.WeekdayNum)
    ) tt
-- на границах может получиться что попало, выкидываем это безобразие
where tt.startingTime < endingTime 
order by dt
23 сен 13, 11:05    [14871319]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
stenford
ну и где тут учет какие именно дно недели учавствуют-то? Ты-ж просто расчитал число полных дней, а надо - в зависимости от того какой именно день недели каждый из дней, т.к. рабочие часы в пятницу и субботу - разные

Слепой программист? Ооооо!!!

Но это просто ТУПОЙ программист.
Какая тебе разница какие? Оне все одинаковые и их @weekcount штук.
23 сен 13, 11:31    [14871525]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
aleks2
Guest
glavryba
Как-то так?


Говнокод не нужен.
23 сен 13, 11:32    [14871531]     Ответить | Цитировать Сообщить модератору
 Re: Как в разнице между временем учитывать только определенные интервалы  [new]
нафсякей,
Guest
set datefirst 1

declare @intervals table(WeekDay tinyint, Start datetime, Finish datetime)

-- business hours
insert into @intervals values(1, '8:30', '19:30')
insert into @intervals values(2, '8:30', '19:30')
insert into @intervals values(3, '8:30', '19:30')
insert into @intervals values(4, '9:00', '21:00')
insert into @intervals values(5, '9:00', '21:00')
insert into @intervals values(6, '9:00', '19:00')
insert into @intervals values(7, '9:00', '19:00')

declare @dt1 datetime, @dt2 datetime
set @dt1='20130715 12:35:44'
set @dt2='20130922 15:45:22'

;with cte1 as (
select 
	 CONVERT(datetime,CONVERT(varchar(8), @dt1, 112)) as dt1_d
	,CONVERT(datetime,CONVERT(varchar(8), @dt1, 108)) as dt1_t
	,CONVERT(datetime,CONVERT(varchar(8), @dt2, 112)) as dt2_d
	,CONVERT(datetime,CONVERT(varchar(8), @dt2, 108)) as dt2_t
),
cte2 as (
select
	 t.dt1_d, i.WeekDay
	,case when i.Start>t.dt1_t then i.Start else t.dt1_t end as Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,t.dt1_d)=i.WeekDay
where t.dt1_t<i.Finish
),
cte3 as (
select
	 t.dt2_d, i.WeekDay
	,case when i.Start>t.dt2_t then i.Start else t.dt2_t end as Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,t.dt2_d)=i.WeekDay
where t.dt2_t<i.Finish
),
cte4 as (
select 
	 dateadd(day,1,t.dt1_d) as dt
	,datepart(weekday,dateadd(day,1,t.dt1_d)) as WeekDay
	,i.Start
	,i.Finish
from cte1 t inner join @intervals i on datepart(weekday,dateadd(day,1,t.dt1_d))=i.WeekDay
where dateadd(day,1,t.dt1_d)<t.dt2_d

union all

select 
	 dateadd(day,1,t.dt) as dt
	,datepart(weekday,dateadd(day,1,t.dt)) as WeekDay
	,i.Start
	,i.Finish
from cte4 t inner join @intervals i on datepart(weekday,dateadd(day,1,t.dt))=i.WeekDay cross join cte1 tt
where dateadd(day,1,t.dt)<tt.dt2_d
)

--select sum(datediff(minute,Start,Finish)) as total
--from
--(
select * from cte2
union all
select * from cte4 
union all
select * from cte3
--) a
23 сен 13, 12:20    [14871881]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить