Считаем рабочие дни

добавлено: 22 мар 10
понравилось:0
просмотров: 6008
комментов: 6

теги:

Автор: DeColo®es

Мало в какой базе данных не возникают проблемы, связанные с тем, что бизнес живет по календарю рабочих дней.

Попробуем их решить в "стиле нормальной реляционной базы данных".

Для обычных дат в SQL Server есть функции dateadd и datepart, позволяющий соотвественно производить арифметические операции над датой/временем и считать разницу в днях, минутах и т.п.

В конце концов, для типов datetime&smalldatetime в отношении астрономических дней доступны операции + и -, а недокументированно - есть еще приведение к float и оперирование временем, как непрерывной величиной.

Но вернемся к нашему бизнесу. Он хочет оперировать с датой также просто, но в разрезе рабочих дней, а зачастую и смешивая астрономическое и "табелное" времяисчисление:
  • Время изготовления изделия состовляет 10 рабочих дней.
  • Срок погашения кредита устанавливается, как 365 дней. При попадании срока погашения на нерабочий день, срок переносится на ближайший следующий рабочий день.
    Совсем хорошо становится, когда вспоминаешь, что рабочие и нерабочие дни на "предприятии" устанавливаются по формуле "с понедельника по пятницу, с учетом постановлений правительства РФ и как установит директор".

    Что же... Начнем.

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

    Создадим таблицу dbo.Calendar с подходящей структурой и заполним ее датами, которых будет достаточно для учета событий всех живущих на данный момент на Земеле людей - с 1890 по 2114 годы Вряд ли кто-то родился раньше и сможет прожить дольше ;):

    create table dbo.Calendar
    	(
    	DateValue datetime not null,
    	IsWorkday tinyint not null constraint DF_Calendar_IsWorkDay default(0),
    	WorkIndex int null,
    	constraint PK_Clendar primary key clustered(DateValue)
    	)
    go
    -- Создаем "индекс по индексу" рабочего дня.
    create nonclustered index IX_Calendar_WorkIndex on dbo.Calendar(WorkIndex, IsWorkday)
    go
    -- Исходим из предположения, что даты в календаре "непрерывны"
    create trigger TR_Calendar_UpdateWorkIndex
    on dbo.Calendar
    after insert, update, delete
    as
    begin
    	if not update(DateValue)
    	and not update(IsWorkday)
    		return
    	set nocount on
    	declare
    		@FirstDate    datetime, -- Первая дата, в которой есть изменение
    		@CurWorkIndex int,
    		@PrevIsWorkday tinyint,
    		@CurIsWorkday tinyint,
    		@CurDateValue datetime
    		
    	select @FirstDate = min(DateValue)
    	from (
    		select i.DateValue
    		from inserted i
    		union all
    		select DateValue
    		from deleted d
    	) q
    	
    	select
    		@PrevIsWorkday = c.IsWorkday,
    		@CurWorkIndex  = WorkIndex		
    	from dbo.Calendar c
    	where c.DateValue = @FirstDate - 1
    	
    	-- Если до рассматриваемой даты нет других дат, то принимаем WorkIndex=0
    	if @CurIsWorkday is null
    		select
    			@CurWorkIndex = 0,
    			@PrevIsWorkday = 1
    		from dbo.Calendar c
    		where c.DateValue = @FirstDate
    	
    	declare CURS cursor local dynamic forward_only for
    		select
    			IsWorkday
    		from dbo.Calendar
    		where DateValue >= @FirstDate
    	open CURS
    	fetch next from CURS into
    		@CurIsWorkday
    
    	while @@FETCH_STATUS = 0
    	begin
    		-- Если текущий день - рабочий, то увеличиваем WorkIndex
    		
    		update dbo.Calendar
    		set @CurWorkIndex = WorkIndex = @CurWorkIndex + @PrevIsWorkday  
    		where current of CURS
    		
    		set @PrevIsWorkday = @CurIsWorkday
    		
    		fetch next from CURS into 
    			@CurIsWorkday
    	end
    		
    end
    go
    -- Вставка всех дат с 1 января 1890 по 16 апреля 2114 года
    -- (Это должно захватить даты рождения и смерти всех живущих в 2010 году.)
    declare @df int
    set @df = @@DATEFIRST
    
    insert into dbo.Calendar(DateValue, IsWorkday) 
    select
    	dateadd(dd, DayIndex, '1890-01-01'),
    	case when (datepart(dw, dateadd(dd, DayIndex, '1890-01-01')) + @df)%7 in(0,1) then 0 else 1 end 
    from 
    	(
    	select v1.number + v2.number * 2048 as DayIndex 
    	from master.dbo.spt_values v1
    	inner join master.dbo.spt_values v2
    		on v2.[type] = 'P'
    		and v2.number <  40
    	where v1.[type] = &apos;P&apos;
    	) q
    go
    Здесь и далее используется синтаксис SQL Server 2005 (а скорее всего - заработает и на 2000)

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

    Теперь немного о структуре таблицы и ее наполнении.

    Поле DateValue - это сама дата.

    Поле IsWorkday указывает на то, является день рабочим или выходным. Возможные значения - 0 или 1, другие "сломают" работу и если есть опасность, можно создать соответствующее ограничение на это поле.

    Поле WorkIndex - самое интересное. Для каждой новой даты, оно возрастает, если предыдущий день рабочий, и остается "прежним", если предыдущий день был выходным. В принципе, можно применять другой алгоритм: оно остается неизменным, если текущий день выходной и увеличивается на 1 для рабочего дня. Его задача быстро производить "вычисления", используя "рабочий" календарь.

    Триггер на таблице поддерживает корректность заполнения поля WorkIndex при изменении данных (например, добавляются новые даты или меняется значение IsWorkday).

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

    Получение разницы в рабочих днях между датами:

    -- Функция возвращает разницу в рабочих днях между датами
    create function dbo.WorkDayDiff(
    	@BeginDate datetime, 
    	@EndDate datetime)
    returns int
    as
    begin
    	return(
    	select
    		e.WorkIndex - b.WorkIndex
    	from dbo.Calendar b
    	inner join dbo.Calendar e
    		on e.DateValue = @EndDate
    	where b.DateValue = @BeginDate
    	)
    end
    go
    select dbo.WorkDayDiff(&apos;2010-03-21&apos;, &apos;2010-03-23&apos;)
    go
    Получение текущего рабочего дня:

    -- Получение текущего операционного (рабочего дня) по дате:
    create function dbo.GetWorkDay(
    	@FromDate datetime)
    returns datetime
    as
    begin
    	return(
    		select 
    			w.DateValue
    		from dbo.Calendar c
    		inner join dbo.Calendar w 
    			on w.WorkIndex = c.WorkIndex
    			and w.IsWorkday = 1
    		where c.DateValue = convert(datetime, convert(varchar(20), @FromDate, 101), 101)
    	) 
    end
    go
    select dbo.GetWorkDay(&apos;2010-03-20&apos;)
    go


    Аналог DATEADD для операций с учетом рабочих дней:

    create function dbo.WorkDateadd(@WorkDays int, @BeginDate datetime)
    returns datetime
    as
    begin
    	return(
    		select 
    			w.DateValue
    		from dbo.Calendar c
    		inner join dbo.Calendar w 
    			on w.WorkIndex = c.WorkIndex + @WorkDays
    				-- Делаем хитро-парадокласльную поправку на то, 
    				-- что если день выходной, то "текущая дата плюс 1 рабочий день" должен стать блажайшим рабочим, а не "следующим".
    				+ case when c.IsWorkday=0 then -1 else 0 end 
    			and w.IsWorkday = 1
    		where c.DateValue = convert(datetime, convert(varchar(20), @BeginDate, 101), 101)
    	)
    end
    go
    select dbo.WorkDateadd(1, &apos;2010-03-22&apos;)


    Конечно, в зависимости от бизнеса, приведенные выше алгоритмы могут быть модифицированы.

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

    Но надеюсь, что суть данного решения понятна и кому-то поможет в его нелегкой борьбе за кусок хлеба на ниве Database Development. ;)


  • Комментарии


    • А почему тип для "IsWorkday" не bit?

    • bit - съедает больше процессорных ресурсов, поскольку до 8 битовых полей записи физически хранятся в одном байте и нужны лишние действия для извлечения и сохранения битового поля по сравнению с однобайтовым tinyint. Поскольку такое "фдаговое" поле всего одно, на хранении мы бы все равно ничего не выиграли.

    • Спасибо, а добавление "соответствующего ограничения" не съест ли выигрышь от этой оптимизации?

    • Нет, ограничение (check constraint) проверяется в момент изменения данных. В случае таблицы - календаря изменения будут происходить максимум несколько раз в год - когда выходит очередной указ о том, какие дни считаются выходными, а какие - рабочими.

    • Всё понял, спасибо!

    • Спасибо! Очень интересный подход, попробую расширить и применить для подразделений в разных регионах России (разные праздники).

      Только @CurIsWorkday же всегда null будет здесь, нет? Имелось ввиду @CurWorkIndex?

      =====
      -- Если до рассматриваемой даты нет других дат, то принимаем WorkIndex=0
      if @CurIsWorkday is null
      =====



    Необходимо войти на сайт, чтобы оставлять комментарии