Цены и дни недели.

В этом посте я бы хотел поговорить о стандартах хранения цен и способах работы с такими стандартами. Представьте, что у вас есть услуга, цена на которую действует все лето. То есть с 1 июня по 31 августа. Разумно было бы хранить ее так.

CREATE TABLE [Costs](
  [id] int NOT NULL PRIMARY KEY
 ,[cost] money NOT NULL
 ,[begin] [smalldatetime] NOT NULL
 ,[end]  [smalldatetime] NOT NULL
);

GO

insert into [Costs] ([id],[cost],[begin],[end])
select 1, 500, convert(smalldatetime,'01.06.2012',104), convert(smalldatetime,'31.08.2012',104)

Давайте приблизим ситуацию к реальности, часто спрос на услуги зависит от дня недели. Например об аренде домика на озере в жаркие выходные стоит подумать заранее, при этом этот же домик с понедельника по четверг, скорее всего, будет стоять пустым. Бизнес это понимает и делает шаг на встречу клиентам, снижая цены по будням.

Теперь два раза в неделю цена на услугу будет изменять. Если мы будем действовать по старой схеме, вместо 1 записи указывающей цену на 3 месяца, придется заводить 2 записи на каждую неделю, которых в летние месяцы более 13(итого более 26 записей). Что довольно неприятно.

Во многих системах работающих в туристическом сегменте является практически стандартом хранения данных в следующем виде.

CREATE TABLE [Costs](
  [id] int NOT NULL PRIMARY KEY
 ,[cost] money NOT NULL
 ,[begin] [smalldatetime] NOT NULL
 ,[end]  [smalldatetime] NOT NULL
 ,[week] char(7) NOT NULL
);

GO

insert into [Costs] ([id],[cost],[begin],[end],[week])
select 1, 500, convert(smalldatetime,'01.06.2012',104), convert(smalldatetime,'31.08.2012',104), '....567'

union all 
select 2, 400, convert(smalldatetime,'01.06.2012',104), convert(smalldatetime,'31.08.2012',104), '1234...'

Поле week хранит дни недели, в которые действует эта цена. Каждый день недели от понедельника до воскресенья пронумерован от единицы до семи соответственно. Для i от 1 до 7, если на i-ой позиции стоит i, то цена действует в этот день недели, если ".", то нет.

Решение неплохое. Позволяет сэкономить место на диске и время сотрудника вносящего информацию о ценах в систему. Осталась одна загвоздка, как преобразовать хранящиеся данные к набору не пересекающихся отрезков, и именно об этом пойдет речь под катом.

Для решения нужно выполнить 5 шагов.

Шаг первый

Указать, что первым днем недели является понедельник, а не воскресенье, как это бывает во многих локализация. Делается это так

SET DATEFIRST 1;

Шаг второй

Нужно получить список целых числе от нуля и до..
Я вижу 2 простых способа сделать это. Если у вас есть доступ к базе master, нет времени и вам не нужны числа более 2048, то

;with [Numbers] as (
  select number [number]
  from  master.dbo.spt_values
  where type = 'p'
)
select * from [Numbers]

Или второй вариант

CREATE TABLE [Numbers](
  [number] [int] NOT NULL PRIMARY KEY
);
GO

insert into Numbers values (0)
.....

Шаг третий

Нужно получить все даты от первого до последнего дня действия цены. Это легко сделать с помощью функции DATEADD

select
    [Costs].[id]
   ,DATEADD(day, [Numbers].[number], [Costs].[begin]) [Date]
from [Costs]
inner join [Numbers]
on [Numbers].[number] <= DATEDIFF(day, [Costs].[begin], [Costs].[end])

Шаг четвертый

Выберем из полученного диапазона данных, только те даты(дни недели), в которые действует цена. А так же пронумеруем оставшиеся строки.

select
    [Costs].[id]
   ,DATEADD(day, [Numbers].[number], [Costs].[begin]) [Date]
   ,ROW_NUMBER() OVER(ORDER BY [Numbers].[number]) [index]
from [Costs]
inner join [Numbers]
 on [Numbers].[number] <= DATEDIFF(day, [Costs].[begin], [Costs].[end])
and CAST(DATEPART(weekday,DATEADD(day, [Numbers].[number], [Costs].[begin])) as char(1)) 
  = SUBSTRING(@WeekDay,DATEPART(weekday,DATEADD(day, [Numbers].[number], [Costs].[begin])),1)

Шаг пятый.

Воспользуемся приемчиком из поста Поиск подряд идущих дат (Островов). А группировать будем по следующей величине.

[Numbers].[number] - ROW_NUMBER() OVER(ORDER BY [Numbers].[number]) 

Результат

В итоге получаем следующий скрипт

;with [Numbers] as (
  select number [number]
  from  master.dbo.spt_values
  where type = 'p'
)
,[Dates] as (
  select
    [Costs].[id]
   ,DATEADD(day, [Numbers].[number], [Costs].[begin]) [Date]
   ,ROW_NUMBER() OVER(ORDER BY [Numbers].[number]) [index]
  from [Costs]
  inner join [Numbers]
    on [Numbers].[number] <= DATEDIFF(day, [Costs].[begin], [Costs].[end])
   and CAST(DATEPART(weekday,DATEADD(day, [Numbers].[number], [Costs].[begin])) as char(1)) 
       = SUBSTRING(@WeekDay,DATEPART(weekday,DATEADD(day, [Numbers].[number], [Costs].[begin])),1)
)
, [Periods] as (
select 
    [id]
   ,min([Date]) [begin]
   ,max([Date]) [end]
from [Dates]
group by [id],[island]
)
select 
   [Periods].*
  ,[Costs].[cost]
from [Costs]
inner join [Periods]
on [Costs].[id] = [Periods].[id]

Злоключение

Хотел бы обратить внимание на несколько деталей.

• Если цена действует все дни без исключения, то поле week должно быть равно '1234567'. Часто пустое значение или значение равно null приравнивают к этому случаю. Для корректной работы в данном случае скрипт должен быть доработан.
• Задача описанная в посте часто рассматривается вместе с задачей поиска цены ну услугу. То есть нас будет интересовать не период действия цены, а период предоставления услуги. Как правило он не превышает месяца, иногда 2-3 (часто ли вы или ваши знакомы уезжали в отпуск на целый месяц? два месяца? полгода? год?). А это значит, что для [Numbers] 2048 значений слишком много и вполне можно обойтись полем [tinyint] и значениями от 0 до 255 или меньше.