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

Откуда:
Сообщений: 1550
Есть такая табличка с историей изменения статуса заявки:

DECLARE @T TABLE
(
	ID INT,
	[OldStatus] VARCHAR(20),
	[NewStatus] VARCHAR(20),
	[DateTimeChanged] DATETIME,
	[PrevDateTimeChanged] DATETIME,
	[TimeInStatus_Minutes] INT
)

INSERT INTO @T
SELECT 10, 'Opened', 'Assigned', '2016-01-11 14:29:11.000','2016-01-11 14:29:10.000',0 UNION
SELECT 10, 'Assigned', 'In Progress', '2016-01-11 15:15:39.000','2016-01-11 14:29:11.000',46 UNION
SELECT 10, 'In Progress', 'Resolved', '2016-01-11 15:51:01.000','2016-01-11 15:15:39.000',36 UNION
SELECT 10, 'Resolved', 'Closed', '2016-01-12 11:23:40.000','2016-01-11 15:51:01.000',1172

SELECT * FROM @T


Также есть табличка - каледарь рабочих дней (отрывок):

DECLARE @CALENDAR TABLE
(
	[Date] [date] NULL,
	[WeekDay] [int] NULL,
	[WorkingHoursStart] [datetime] NULL,
	[WorkingHoursEnd] [datetime] NULL
)

INSERT INTO @CALENDAR

SELECT '2016-01-10', 7, NULL, NULL UNION
SELECT '2016-01-11', 1, '2016-01-11 09:00:00.000', '2016-01-11 19:00:00.000' UNION
SELECT '2016-01-12', 2, '2016-01-12 09:00:00.000', '2016-01-12 19:00:00.000'

SELECT * FROM @CALENDAR


Как сделать такую выборку, чтобы показывались количество ЧАСОВ которые заявка провела в одном статусе (т.е грубо говоря DATEDIFF(между [DateTimeChanged] и [PrevDateTimeChanged]), но приэтом учитывались только РАБОЧИЕ ЧАСЫ дня, которые как раз содержатся в @CALENDAR? т.е. к пример для последней записи понятно, что прежде чем закрыться, заявка провела в статусе Resolved около 20ти часов, при этом 11 января с 15.51 до 19.00 (это округленно 3 часа) + 12 января с 9.00 утра до момента закрытия 11.23 (это округленно 2 часа), т.е вот эту сумму 3 + 2 = 5 часов провела заявка в статусе Resolved - нужно отобразить в выборке...Если [WorkingHoursStart] в @CALENDAR не проставлен - значит это выходной день, и такие часы в выборку попадать не должны..

К сообщению приложен файл. Размер - 12Kb
20 янв 16, 14:33    [18706138]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
SQL2008
Member

Откуда: Москва
Сообщений: 4539
1. Посчитайте общее количество часов выполнения заявки.
2. Вычислите количество нерабочих часов, попадающий в период п.1
3. Вычтите из п.1 значение из п.2
20 янв 16, 14:38    [18706167]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
Santa89
Member

Откуда:
Сообщений: 1550
SQL2008,

алгоритм понятен, никак не могу понять как правильно сделать джойн с календарем чтобы посчитать количество рабочих часов заявки.
ведь заявка может быть открыта в пятницу, а закрыта в понедельник, и провисеть с нерабочими часами СБ и ВС, в каледаре эти даты есть, но у них [WorkingHoursStart] и [WorkingHoursEnd] = NULL

просто джойн по [DateTimeChanged] = [WorkingHoursStart] не пойдёт..
20 янв 16, 14:47    [18706223]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
Santa89
алгоритм понятен, никак не могу понять как правильно сделать джойн с календарем чтобы посчитать количество рабочих часов заявки.
ведь заявка может быть открыта в пятницу, а закрыта в понедельник, и провисеть с нерабочими часами СБ и ВС, в каледаре эти даты есть, но у них
Наверное, как то так:
DECLARE @T TABLE
(
	ID INT,
	[OldStatus] VARCHAR(20),
	[NewStatus] VARCHAR(20),
	[DateTimeChanged] DATETIME,
	[PrevDateTimeChanged] DATETIME,
	[TimeInStatus_Minutes] INT
)

INSERT INTO @T
SELECT 10, 'Opened', 'Assigned', '2016-01-11 14:29:11.000','2016-01-11 14:29:10.000',0 UNION
SELECT 10, 'Assigned', 'In Progress', '2016-01-11 15:15:39.000','2016-01-11 14:29:11.000',46 UNION
SELECT 10, 'In Progress', 'Resolved', '2016-01-11 15:51:01.000','2016-01-11 15:15:39.000',36 UNION
SELECT 10, 'Resolved', 'Closed', '2016-01-12 11:23:40.000','2016-01-11 15:51:01.000',1172

SELECT * FROM @T order by PrevDateTimeChanged

DECLARE @CALENDAR TABLE
(
	[Date] [date] NULL,
	[WeekDay] [int] NULL,
	[WorkingHoursStart] [datetime] NULL,
	[WorkingHoursEnd] [datetime] NULL
)

INSERT INTO @CALENDAR

SELECT '2016-01-10', 7, NULL, NULL UNION
SELECT '2016-01-11', 1, '2016-01-11 09:00:00.000', '2016-01-11 19:00:00.000' UNION
SELECT '2016-01-12', 2, '2016-01-12 09:00:00.000', '2016-01-12 19:00:00.000'

SELECT * FROM @CALENDAR order by Date

select t.ID, t.OldStatus, t.NewStatus, t.PrevDateTimeChanged, t.DateTimeChanged, 
	SUM(DATEDIFF(mi, 
			case when t.PrevDateTimeChanged > c.WorkingHoursStart then t.PrevDateTimeChanged else c.WorkingHoursStart end, 
			case when t.DateTimeChanged < c.WorkingHoursEnd then t.DateTimeChanged else c.WorkingHoursEnd end
		)
	) as [Status in Minutes]
from @T as t
	left join @CALENDAR as c
		on c.[Date] between convert(date, t.PrevDateTimeChanged) and convert(date, t.[DateTimeChanged])
group by t.ID, t.OldStatus, t.NewStatus, t.PrevDateTimeChanged, t.DateTimeChanged
20 янв 16, 15:32    [18706540]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
Santa89
Member

Откуда:
Сообщений: 1550
alexeyvg,

супер!
только вот если преположить что среда 2016-01-13 была праздником (в это время ненужно учитывать часы) то таблицы изменятся так:

DECLARE @T TABLE
(
	ID INT,
	[OldStatus] VARCHAR(20),
	[NewStatus] VARCHAR(20),
	[DateTimeChanged] DATETIME,
	[PrevDateTimeChanged] DATETIME,
	[TimeInStatus_Minutes] INT
)

INSERT INTO @T
SELECT 10, 'Opened', 'Assigned', '2016-01-11 14:29:11.000','2016-01-11 14:29:10.000',0 UNION
SELECT 10, 'Assigned', 'In Progress', '2016-01-11 15:15:39.000','2016-01-11 14:29:11.000',46 UNION
SELECT 10, 'In Progress', 'Resolved', '2016-01-11 15:51:01.000','2016-01-11 15:15:39.000',36 UNION
SELECT 10, 'Resolved', 'Closed', '2016-01-14 11:23:40.000','2016-01-11 15:51:01.000',1172


и

DECLARE @CALENDAR TABLE
(
	[Date] [date] NULL,
	[WeekDay] [int] NULL,
	[WorkingHoursStart] [datetime] NULL,
	[WorkingHoursEnd] [datetime] NULL
)

INSERT INTO @CALENDAR

SELECT '2016-01-10', 7, NULL, NULL UNION
SELECT '2016-01-11', 1, '2016-01-11 09:00:00.000', '2016-01-11 19:00:00.000' UNION
SELECT '2016-01-12', 2, '2016-01-12 09:00:00.000', '2016-01-12 19:00:00.000' UNION
SELECT '2016-01-13', 3, NULL, NULL UNION
SELECT '2016-01-14', 4, '2016-01-14 09:00:00.000', '2016-01-14 19:00:00.000'


получится что в последнем статусе заявка провисела 932, а по идее должна остаться как 332, ведь выходной день Среду мы не учитываем
20 янв 16, 15:52    [18706687]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
Santa89
Member

Откуда:
Сообщений: 1550
Извиняюсь, перескочил через одну дату.
Действительно, если предположить что скажем 12 января - выходной, и заявка закрылась не 12го а 13го - выходные часы не учитываются.

alexeyvg,
огромное спасибо! Буду практиковаться
20 янв 16, 15:55    [18706721]     Ответить | Цитировать Сообщить модератору
 Re: T-SQL. Учитывать только рабочие часы, как?  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32173
Santa89,
У меня, возможно, неправильно учитываются WorkingHoursStart, если они NULL, может прибавляться несколько часов.
Но в общем идею вы поняли; может, нужно внимательно сделать эту проверку, и внимательно выставить точные границы (с равенством или без).
20 янв 16, 17:16    [18707282]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить