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

Откуда:
Сообщений: 204
Здраствуйте, подскажите как выбрать время по интервалу. Есть такая таблица:
DECLARE @SCHEDULE TABLE(TimeStart TIME, TimeEnd TIME, Interval INT)
INSERT @SCHEDULE(TimeStart, TimeEnd, Interval)
VALUES
('08:00', '09:30', 15),
('12:00', '13:30', 15),
('15:30', '18:00', 15),
('18:30', '20:00', 15)
SELECT TimeStart, TimeEnd, Interval FROM @SCHEDULE

Нужно получить время с нарастанием по интервалу, например:
Appointment
08:00
08:15
08:30
08:45
09:00
и т.д.
17 окт 13, 05:45    [14983278]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Torre,

Создать таблицу интервалов и джойнить с ней.

DECLARE @SCHEDULE TABLE(TimeStart TIME, TimeEnd TIME, Interval INT)
INSERT @SCHEDULE(TimeStart, TimeEnd, Interval)
VALUES
('08:00', '09:30', 15),
('12:00', '13:30', 15),
('15:30', '18:00', 15),
('18:30', '20:00', 15)

declare @tblIntervals table(TimeStart TIME, TimeEnd TIME, Interval INT)

declare @t time='00:00'
declare @i int=15

while @t<='23:30'
begin
	insert into @tblIntervals(TimeStart, TimeEnd,Interval)
	values(@t,DATEADD(MINUTE,@i,@t),@i)
	
	set @t=DATEADD(MINUTE,@i,@t)
end


SELECT i.*
FROM @SCHEDULE s
	 join @tblIntervals i on i.TimeStart>=s.TimeStart and i.TimeEnd<=s.TimeEnd
WHERE i.Interval=15
ORDER BY i.TimeStart
17 окт 13, 06:18    [14983293]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
aleks2
Guest
1. Создай постоянную таблицу натуральных чисел.
2. И не парься.
declare @nn table(n int primary key clustered);
insert @nn values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)

DECLARE @SCHEDULE TABLE(TimeStart TIME, TimeEnd TIME, Interval INT)
INSERT @SCHEDULE(TimeStart, TimeEnd, Interval)
VALUES
('08:00', '09:30', 15),
('12:00', '13:30', 15),
('15:30', '18:00', 15),
('18:30', '20:00', 15)
SELECT TimeStart, TimeEnd, Interval FROM @SCHEDULE


SELECT dateadd( minute, (n.n-1)*s.Interval, s.TimeStart) App FROM @SCHEDULE s inner join @nn n on n.n between 1 and datediff(minute, s.TimeStart, s.TimeEnd)/s.Interval + 1
17 окт 13, 06:24    [14983298]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Спасибо, Ruuu, понял реализацию, пожалуй, дополнительная таблица здесь более кстати. Как я понимаю, остаются реализация с курсором и СТЕ, но производительность будет хуже. Вот только сколько значений добавить в таблицу интервалов, полагаю 288 будет достаточно, при условии минимального интервала 5 минут в сутки.

Код aleks2 до конца не понял, от join я в шоке, никогда бы не подумал, что так можно сделать, блестяще, aleks2! Это надо в сборник SQL добавить

Спасибо, вы мне очень помогли!
17 окт 13, 22:39    [14988270]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Всем привет! Появилась теперь необходимость убрать из результатов последнюю строку, т.к. она является завершающей точкой времени. Связанные с результатом запросы по этой причине выводят лишнюю строку, как бы это пофиксить?
11 ноя 13, 03:11    [15106477]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Torre
Появилась теперь необходимость убрать из результатов последнюю строку, т.к. она является завершающей точкой времени.
Посчитать число строк N, которое вернул запрос, и выбрать из того же запроса TOP N-1. Но это какой-то ректальный метод, а лучше вряд ли есть.
Лучше пересмотреть логику. Я что-то не могу представить ситуацию, когда в расписании " необходимо убрать последнюю строку, т.к. она является завершающей точкой времени". Это вы, например, у врача не учитываете его последнее время приёма, потому что он всё-равно в конце дня уже ничего не соображает от усталости, а значит это время можно не учитывать?
11 ноя 13, 03:38    [15106489]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Спасибо что откликнулись, совершенно верно все обстоит именно так, каждая строка указывает на начало интервала, соответственно последняя строка не нужна в запросе.
11 ноя 13, 03:45    [15106494]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Последнюю строку не могу удалить, некоторые запросы используют несколько интервалов.
11 ноя 13, 03:47    [15106495]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Ruuu
Member

Откуда: Иркутск
Сообщений: 4272
Torre
Спасибо что откликнулись, совершенно верно все обстоит именно так, каждая строка указывает на начало интервала, соответственно последняя строка не нужна в запросе.
Вы меня неправильно поняли, и я вас не понимаю. Лучше наверное написать пример.
Ну, или сделайте, как я уже написал выше. Но это скорее всего неправильно.
11 ноя 13, 03:56    [15106500]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
invm
Member

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

Возьмите пример от aleks2 и замените
between 1 and datediff(minute, s.TimeStart, s.TimeEnd)/s.Interval + 1
на
between 1 and datediff(minute, s.TimeStart, s.TimeEnd)/s.Interval
11 ноя 13, 09:21    [15106844]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
gang
Member

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

Попробуйте так
set nocount on
declare @start datetime
declare @end datetime
declare @step int
select 
 @start='08:00'
,@end  ='09:30'
,@step =15
;with cte (gap) as 
(select @start gap union all select dateadd(mi,@step,gap) from cte where gap<dateadd(mi,-@step,@end)  ) 
select 
substring (convert (varchar(30), gap,120),12,5) as Appointment
from cte OPTION (MAXRECURSION 0)
11 ноя 13, 11:19    [15107400]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
gang, красивая рекурсия, только проблема в том, что у меня на входе таблица интервалов:

PersonId TimeStart TimeEnd Interval
53 12:00:0013:00:00 30
53 15:30:0018:00:00 30
53 18:30:0019:00:00 15
53 13:30:0015:00:00 30

Попытался сам написать запрос, но не могу справится хотя решение где-то рядом, но не могу справится с датой, намекни просто, как мне написать вторую половину рекурсии Recursive member:
WITH CTE(PersonId, GAP)
AS
(
-- Anchor member
    SELECT S.PersonId, S.TimeStart, S.TimeEnd, S.Interval FROM SCHEDULE AS S
    UNION ALL
-- Recursive member
    SELECT ...как здесь написать?
)
SELECT * FROM CTE INNER JOIN...


Идею понял, но уже поздно, не осилю, просто напиши псевдокодом, как вторую половину следует написать, спасибо тебе.
12 ноя 13, 01:07    [15112564]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
gang
Member

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

ну в первом приближении нужно оформить в виде функции и аппалаить (пардон за птичий) ее исходной таблицей.
Примерно так
create FUNCTION _for_delete_test_times_from_interval (@start datetime, @end datetime, @step int)
RETURNS @Appointments TABLE (Appointment varchar(9))
AS
begin

with cte (gap) as 
(select @start gap union all select dateadd(mi,@step,gap) from cte where gap<dateadd(mi,-@step,@end)  ) 

insert @Appointments
select 
substring (convert (varchar(30), gap,120),12,8) as Appointment
from cte OPTION (MAXRECURSION 0)
return
end;


create table #t (
PersonId int
,TimeStart varchar(8)	 
,TimeEnd  varchar(8) 
,Interval int
)
insert #t values (53,'12:00:00','13:00:00',30)
insert #t values (53,'15:30:00','18:00:00',30)
insert #t values (53,'18:30:00','19:00:00',15)
insert #t values (53,'13:30:00','15:00:00',30)

select * from #t cross apply _for_delete_test_times_from_interval (TimeStart, TimeEnd,Interval)


Не очень понял что там должно быть на выходе в плане включения окончания интервалов, но можно уже по месту допилить.
12 ноя 13, 09:07    [15113019]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
gang, пришлось напрячься и залезть в доки, но все получилось. Огромная тебе благодарность, эта рекурсия съэкономила мне много времени и кода, спасибо тебе за помощь, надеюсь этот топик многим еще пригодиться.
13 ноя 13, 05:16    [15119061]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
aleks2
Guest
Torre
gang, пришлось напрячься и залезть в доки, но все получилось. Огромная тебе благодарность, эта рекурсия съэкономила мне много времени и кода, спасибо тебе за помощь, надеюсь этот топик многим еще пригодиться.


Глупенький, любить надо не себя, а сервер.
Стрельба из пушки по мухам ведет только к лишней нагрузке на систему.

Поэтому "для многих" этот топик должен являть пример "как делать не надо".
13 ноя 13, 05:34    [15119066]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Да, я нуб в скуле, но что поделать, не много я с ним работаю, поэтому и приходится обращаться. Согласен твоя логика лучше, но у меня и скилы не те, чтобы свободно мыслить на таком уровне. Мне важнее выполнить задачу быстрее, лучше я на другом участке кода время потеряю, но задача будет выполнена в срок, в данном случае это важнее. Не думай, что твой код прошел мимо меня, я его отложил на будущее.
13 ноя 13, 18:55    [15124138]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Приветствую всех. Приходится продолжить тему. Условия задачи поменялись, теперь надо получить интервалы времени, которых нет в таблице @SCHEDULE. Таблица @WORKDAYS задает продолжительность дня, таблица @SCHEDULE задает интервалы, которые не должны участвовать в конечном результате запроса.
DECLARE @WORKDAYS TABLE (WorkDayId INT IDENTITY(1,1) NOT NULL, DayStart TIME, DayEnd Time, Interval INT)
INSERT @WORKDAYS(DayStart, DayEnd, Interval)
VALUES
('07:00', '20:00', 30)
DECLARE @SCHEDULE TABLE(WorkDayId INT, TimeStart TIME, TimeEnd TIME)
INSERT @SCHEDULE(WorkDayId, TimeStart, TimeEnd)
VALUES
(1, '08:00', '09:30'),
(1, '12:00', '13:30'),
(1, '15:30', '18:00'),
(1, '18:30', '19:30')

SELECT W.DayStart, W.DayEnd, W.Interval, S.TimeStart, S.TimeEnd FROM @WORKDAYS AS W
 INNER JOIN @SCHEDULE AS S ON W.WorkDayId = S.WorkDayId

Необходимо получить такой результат:
WorkDayId Times
1 07:00
1 07:30
1 09:30
1 10:00
1 10:30
1 11:00
1 11:30
1 13:30
1 14:00
1 14:30
1 15:00
1 18:00
1 19:30


Таблицу все интервалов согласно примеру от aleks2 я составил:
DECLARE @NN TABLE(N INT);
DECLARE @NID INT = 1;WHILE @NID < 288 BEGIN;INSERT @NN VALUES (@NID);SET @NID = @NID + 1;END;	
SELECT W.WorkDayId, DATEADD(MINUTE, (N.N - 1) * W.Interval, W.DayStart) AS TIMES FROM @WORKDAYS AS W
INNER JOIN @NN N ON N.N BETWEEN 1 AND (DATEDIFF(MINUTE, W.DayStart, W.DayEnd) / W.Interval) + 1

Теперь нужно выбрать из этого запроса интервалы, которые отсутствуют в таблице @SCHEDULE, как это сделать, использовал BETWEEN и <>, но результат неверный, подскажите, какой правильный подход к созданию такого запроса?
16 ноя 13, 14:45    [15141345]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Guf
Member

Откуда: Новосибирск
Сообщений: 649
Torre
Теперь нужно выбрать из этого запроса интервалы, которые отсутствуют в таблице @SCHEDULE

EXCEPT?
17 ноя 13, 10:51    [15143091]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
qwerty112
Guest
Torre
Теперь нужно выбрать из этого запроса интервалы, которые отсутствуют в таблице @SCHEDULE, как это сделать, использовал BETWEEN и <>, но результат неверный, подскажите, какой правильный подход к созданию такого запроса?

not exists
17 ноя 13, 11:29    [15143122]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
select d.*, dt
from @workdays d
join master..spt_values v on v.type='P' and v.number<datediff(minute,d.daystart,d.dayend)/d.interval
cross apply(select dt=dateadd(minute,d.interval*v.number,d.daystart))dt
where not exists(
  select 1
  from @SCHEDULE s
  where s.WorkDayId=d.WorkDayId and dt between s.TimeStart and s.TimeEnd
  )
17 ноя 13, 11:30    [15143124]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
Спасибо всем за помощь, но необходимого результата еще не получил. Guf и qwerty112 EXCEPT и EXISTS не возвращают пока точный результат, только близкий:

DECLARE @NN TABLE(N INT);SET NOCOUNT ON;
DECLARE @NID INT = 1;WHILE @NID < 288 BEGIN;INSERT @NN VALUES (@NID);SET @NID = @NID + 1;END;
SELECT D.WorkDayId, D.TIMES FROM
(SELECT W.WorkDayId, DATEADD(MINUTE, (N.N - 1) * W.Interval, W.DayStart) AS TIMES FROM #WORKDAYS AS W
INNER JOIN @NN N ON N.N BETWEEN 1 AND (DATEDIFF(MINUTE, W.DayStart, W.DayEnd) / W.Interval) + 1 
EXCEPT
SELECT W.WorkDayId, DATEADD(MINUTE, (N.N - 1) * W.Interval, S.TimeStart) AS TIMES FROM #WORKDAYS AS W
INNER JOIN #SCHEDULE AS S ON W.WorkDayId = S.WorkDayId
INNER JOIN @NN N ON N.N BETWEEN 1 AND (DATEDIFF(MINUTE, S.TimeStart, S.TimeEnd) / W.Interval) + 1
) AS D
ORDER BY D.TIMES


Логику запроса от Cygapb-007 еще не понял, но этот способ показался мне интересным, работаю пока в этом направлении, спасибо, Cygapb-007.
17 ноя 13, 11:52    [15143156]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
qwerty112
Guest
Torre,

+
DECLARE @WORKDAYS TABLE (WorkDayId INT IDENTITY(1,1) NOT NULL, DayStart datetime, DayEnd datetime, Interval INT)
INSERT @WORKDAYS(DayStart, DayEnd, Interval)VALUES('07:00', '20:00', 30)

DECLARE @SCHEDULE TABLE(WorkDayId INT, TimeStart datetime, TimeEnd datetime)
INSERT @SCHEDULE(WorkDayId, TimeStart, TimeEnd)VALUES(1, '08:00', '09:30')
INSERT @SCHEDULE(WorkDayId, TimeStart, TimeEnd)VALUES(1, '12:00', '13:30')
INSERT @SCHEDULE(WorkDayId, TimeStart, TimeEnd)VALUES(1, '15:30', '18:00')
INSERT @SCHEDULE(WorkDayId, TimeStart, TimeEnd)VALUES(1, '18:30', '19:30')

--SELECT W.DayStart, W.DayEnd, W.Interval, S.TimeStart, S.TimeEnd FROM @WORKDAYS AS W
-- INNER JOIN @SCHEDULE AS S ON W.WorkDayId = S.WorkDayId

DECLARE @NN TABLE(N INT);
DECLARE @NID INT 
set @NID= 1;
WHILE @NID < 288 
BEGIN;
	INSERT @NN VALUES (@NID);
	SET @NID = @NID + 1;
END;

SELECT W.WorkDayId, DATEADD(MINUTE, (N.N - 1) * W.Interval, W.DayStart) AS TIMES 
FROM @WORKDAYS AS W
INNER JOIN @NN N ON N.N BETWEEN 1 AND (DATEDIFF(MINUTE, W.DayStart, W.DayEnd) / W.Interval) + 1
where not exists 
	(select 1 from @SCHEDULE s where W.WorkDayId=s.WorkDayId 
	and DATEADD(MINUTE, (N.N - 1) * W.Interval, W.DayStart)>=s.TimeStart and DATEADD(MINUTE, (N.N - 1) * W.Interval, W.DayStart)<s.TimeEnd)

WorkDayId   TIMES
----------- -----------------------
1           1900-01-01 07:00:00.000
1           1900-01-01 07:30:00.000
1           1900-01-01 09:30:00.000
1           1900-01-01 10:00:00.000
1           1900-01-01 10:30:00.000
1           1900-01-01 11:00:00.000
1           1900-01-01 11:30:00.000
1           1900-01-01 13:30:00.000
1           1900-01-01 14:00:00.000
1           1900-01-01 14:30:00.000
1           1900-01-01 15:00:00.000
1           1900-01-01 18:00:00.000
1           1900-01-01 19:30:00.000
1           1900-01-01 20:00:00.000

(14 row(s) affected)
17 ноя 13, 12:13    [15143189]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
qwerty112, благодарю! Вот только 20:00 бы убрать из результатов, сейчас сам попробую справиться, большое спасибо!
17 ноя 13, 12:38    [15143231]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
qwerty112
Guest
Torre
qwerty112, благодарю! Вот только 20:00 бы убрать из результатов, сейчас сам попробую справиться, большое спасибо!

а что ж там справляться - 15106844
17 ноя 13, 12:44    [15143237]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, как получить время по интервалу  [new]
Torre
Member

Откуда:
Сообщений: 204
qwerty112, точно, я уже наверное перегрелся, очевидного не понимаю. В любом случае спасибо вам, именно такую реализацию я и искал, красиво и точно, благодарю, сам бы вряд ли до этого дошел.
17 ноя 13, 12:52    [15143262]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить