Увеличение времени на разные промежутки / Microsoft SQL Server

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Увеличение времени на разные промежутки

12 сообщений из 12, страница 1 из 1

Увеличение времени на разные промежутки

#40129462

STestS

Гость

Здравствуйте.
Есть дата от которой нужно сделать несколько промежутков и количество дней. В общем должно получиться два периода (начало, конец).

Код: sql

1.
2.
3.
4.
5.

Begin	                End
20.01.2022 00:00:00	20.01.2022 07:00:00
20.01.2022 15:30:00	21.01.2022 00:00:00
21.01.2022 00:00:00	21.01.2022 07:00:00
21.01.2022 15:30:00	22.01.2022 00:00:00

Я понимаю что нужно создать таблицу и к дате добавлять

Код: sql

select DATEADD(MINUTE, 0, (select DATEADD(HOUR, 7, @StartDate)))

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

...

Рейтинг:

0 / 0

26.01.2022, 09:20

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129464

Akina

Участник

Откуда: Зеленоград, Москва, Россия

Сообщения: 20 644

Рейтинг: 0 / 0

Ну например так...

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

WITH cte AS (
    SELECT '2022-01-20' AS startdate 
    UNION ALL
    SELECT '2022-01-21'
)
SELECT startdate AS [Begin], DATEADD(HOUR, 7, startdate) [End]
FROM cte
UNION ALL
SELECT DATEADD(MINUTE, 15*60+30, startdate), DATEADD(DAY, 1, startdate)
FROM cte
ORDER BY 1

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7a052b7d2076555c4284ecf45acdbd24

...

Рейтинг:

0 / 0

26.01.2022, 09:28

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129470

STestS

Гость

Akina,

Да именно то что нужно спасибо!

...

Рейтинг:

0 / 0

26.01.2022, 09:56

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129548

STestS

Гость

Akina,

все же я не до конца проверил... При выборе нескольких дней (с 20 по 25) данные не те что должны быть...

Код: sql

1.
2.
3.
4.
5.

Begin               	End
2022-01-20 00:00:00.000	2022-01-20 07:00:00.000
2022-01-20 15:30:00.000	2022-01-21 00:00:00.000
2022-01-25 00:00:00.000	2022-01-25 07:00:00.000
2022-01-25 15:30:00.000	2022-01-26 00:00:00.000

...

Рейтинг:

0 / 0

26.01.2022, 12:30

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129554

Konst_One

Участник

Сообщения: 12 099

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

Declare @dStart datetime = '20220120', 
        @dEnd datetime = '20220125';
        
;with cte as 
(
	select 100 * l + 10 * m + n as n
	from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t1(l) cross join
		 (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t2(m) cross join
		 (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t3(n) 
	where 100 * l + 10 * m + n <= datediff(DAY, @dStart, @dEnd)     
)
select DATEADD(DAY, n, @dStart) AS [Begin], DATEADD(HOUR, 7, dateadd(DAY, n, @dStart)) [End]
from cte
UNION ALL
SELECT DATEADD(MINUTE, 15*60+30, DATEADD(DAY, n, @dStart)), DATEADD(DAY, 1, DATEADD(DAY, n, @dStart))
FROM cte
order by 1;

...

Рейтинг:

0 / 0

26.01.2022, 12:45

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129572

Wlr-l

Гость

Без рекурсии:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.

with A    (    dt) as (
 select '20220120'  union all
 select '20220121'  union all
 select '20220122'
)
, B     (       b,       e ) as (
    select '00:00', '07:00' union all
    select '15:30', '00:00' 
)
select dateadd(minute, datediff(minute, '00:00', b), dt),
       dateadd(minute, datediff(minute, '00:00', e), dt)
  from       A
  cross join B
  order by 1

Результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.

beg			end
2022-01-20 00:00:00.000	2022-01-20 07:00:00.000
2022-01-20 15:30:00.000	2022-01-20 00:00:00.000
2022-01-21 00:00:00.000	2022-01-21 07:00:00.000
2022-01-21 15:30:00.000	2022-01-21 00:00:00.000
2022-01-22 00:00:00.000	2022-01-22 07:00:00.000
2022-01-22 15:30:00.000	2022-01-22 00:00:00.000

...

Рейтинг:

0 / 0

26.01.2022, 13:18

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129576

Wlr-l

Гость

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

...

Рейтинг:

0 / 0

26.01.2022, 13:26

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129581

STestS

Гость

Спасибо за ответы. Помогло. Но вот как быть когда нужно более мельче раздробить промежуток? Я так понимаю универсального решения нету

Код: sql

1.
2.
3.
4.
5.
6.
7.

Begin	                End
20.01.2022 00:00:00	20.01.2022 07:00:00
20.01.2022 07:00:00	20.01.2022 15:30:00
20.01.2022 15:30:00	21.01.2022 00:00:00
21.01.2022 00:00:00	21.01.2022 07:00:00
21.01.2022 07:00:00	21.01.2022 15:30:00
21.01.2022 15:30:00	22.01.2022 00:00:00

...

Рейтинг:

0 / 0

26.01.2022, 13:33

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129585

Wlr-l

Гость

STestS,

22344201

...

Рейтинг:

0 / 0

26.01.2022, 13:44

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40129602

Wlr-l

Гость

Соединив мое предыдущее решение с решением по ссылке получим:

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

with A    (     dt)  as ( -- календарь
  select '20220120'  union all
  select '20220121'  union all
  select '20220122'
)
, B     (      b,    T) as ( --смены (начало, длительность в минутах)
  select '00:00',  420  union all
  select '07:00',  510  union all
  select '15:30',  510 
)

select dt as [дата]
      ,dateadd(minute, datediff(minute, '00:00', b), dt)                      as [начало смены]
      ,dateadd(minute, T, dateadd(minute, datediff(minute, '00:00', b), dt))  as [конец смены]
 from       A
 cross join B
order by 2;

Результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

дата		начало смены			конец смены
20220120	2022-01-20 00:00:00.000	2022-01-20 07:00:00.000
20220120	2022-01-20 07:00:00.000	2022-01-20 15:30:00.000
20220120	2022-01-20 15:30:00.000	2022-01-21 00:00:00.000
20220121	2022-01-21 00:00:00.000	2022-01-21 07:00:00.000
20220121	2022-01-21 07:00:00.000	2022-01-21 15:30:00.000
20220121	2022-01-21 15:30:00.000	2022-01-22 00:00:00.000
20220122	2022-01-22 00:00:00.000	2022-01-22 07:00:00.000
20220122	2022-01-22 07:00:00.000	2022-01-22 15:30:00.000
20220122	2022-01-22 15:30:00.000	2022-01-23 00:00:00.000

Как видим, ничего сложного здесь нет. Можно дробить интервалы до минут.

...

Рейтинг:

0 / 0

26.01.2022, 14:27

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40130880

Wlr-l

Гость

В частной беседе был задан вопрос: "Нельзя ли обойтись без явного задания длительности смены, ведь известно же начало следующей смены?". Отвечу здесь, так как это продолжение моего предыдущего сообщения. Можно, для этого нужно найти разность времени начал следующей и текущей смены. Есть только трудность в нахождении длительности последней смены, так как нельзя задать время '24:00'. Под сменой понимается интервал времени в течение одних суток. Для упрощения рассмотрения точку времени, разделяющую смены, отнесем к обеим сменам.

У нас есть календарь и таблица начала смен. Пусть у нас будут 4 смены. Смены могут быть разной длительности и не обязательно должны начинаться в полночь.

Найдем начало и конец каждой смены, а также длительность смен.

Представим время начала смен как дату со временем. Конкретная дата не имеет значение, поэтому пусть это будет дата по умолчанию. Далее найдем начало первой смены следующего дня. Это происходит в запросе С. Теперь можно найти длительность всех смен (запрос D).

Функция first_value взята для наглядности. В этом месте можно взять минимальное значение B.b. Тогда запросы C и D можно объединить в один запрос, но получим громоздкое выражение для Т.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.

with A    (     dt)  as ( -- календарь
  select '20220120'  union all
  select '20220121'  union all
  select '20220122'
)
, B     (      b) as ( --смены (начало)
  select '01:00'  union all --начало 1-й смены
  select '02:00'  union all --начало 2-й смены
  select '08:15'  union all --начало 3-й смены
  select '17:00'            --начало 4-й смены
)
, C as (
  select b                                                      --начало смены как время
       , cast(b as datetime2)                             as bb --начало смены как дата
       , dateadd(day, 1, first_value(b) over(order by b)) as f  --начало первой смены следующего дня как дата
   from B
)  ---  select * from C;
, D as (
  select b                                                          --начало смены как время
       , datediff(minute, bb, lead(bb, 1, f) over(order by b)) as T --длительность смены в минутах
    from C
)   ---  select * from D;

select cast(dt as date)                                                                            as [дата]
     , cast(dateadd(minute, datediff(minute, '00:00', b), dt)                     as datetime2(0)) as [начало смены]
     , cast(dateadd(minute, T, dateadd(minute, datediff(minute, '00:00', b), dt)) as datetime2(0)) as [конец смены]
     , T                                                                                           as Tm
     , cast(dateadd(minute, T, '00:00') as time(0))                                                as Thms
 from       A
 cross join D
 order by 2;

Результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

дата		начало смены		конец смены		Tm	Thms
2022-01-20	2022-01-20 01:00:00	2022-01-20 02:00:00	60	01:00:00
2022-01-20	2022-01-20 02:00:00	2022-01-20 08:15:00	375	06:15:00
2022-01-20	2022-01-20 08:15:00	2022-01-20 17:00:00	525	08:45:00
2022-01-20	2022-01-20 17:00:00	2022-01-21 01:00:00	480	08:00:00
2022-01-21	2022-01-21 01:00:00	2022-01-21 02:00:00	60	01:00:00
2022-01-21	2022-01-21 02:00:00	2022-01-21 08:15:00	375	06:15:00
2022-01-21	2022-01-21 08:15:00	2022-01-21 17:00:00	525	08:45:00
2022-01-21	2022-01-21 17:00:00	2022-01-22 01:00:00	480	08:00:00
2022-01-22	2022-01-22 01:00:00	2022-01-22 02:00:00	60	01:00:00
2022-01-22	2022-01-22 02:00:00	2022-01-22 08:15:00	375	06:15:00
2022-01-22	2022-01-22 08:15:00	2022-01-22 17:00:00	525	08:45:00
2022-01-22	2022-01-22 17:00:00	2022-01-23 01:00:00	480	08:00:00

...

Рейтинг:

0 / 0

31.01.2022, 23:09

| Ответить | Цитировать | Написать

Увеличение времени на разные промежутки

#40131086

Wlr-l

Гость

Сегодня задали следующий вопрос: "Как быть, если первая смена начинается в 8:00, а все последующие смены, если даже они заканчиваются или начинаются после смены даты, все равно относятся к дате начала первой смены?".

Эта задача чуть сложнее предыдущей. Придется покрутить стрелку часов. Сначала против часовой стрелки так, чтобы начало 1-й смены совпало с началом суток. Вычислить длительность смен. После этого вернуть назад стрелку часов и завершить вычисления. Все вычисления я разбил для наглядности на простые шаги.

Календарь остался тот же, только явно задан тип данных, чтобы в запросе было меньше преобразований типов. В таблицу смен добавился столбик с номером смены, а время начала смены получило тип. При конвертации времени к дате, она получает по умолчанию дату 1900-01-01.

Осталось найти начало и конец каждой смены, а также длительность смен. Да, термин смена не обязательно относится к производственной смене. Это просто интервал времени.

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.

with A    (     dt)  as ( -- календарь
  select cast(dt as datetime2(0)) dt
    from (values ('20220120'),
                 ('20220121'),
                 ('20220122')
         )  as T (dt)
)
, B as ( --смены (начало)
  select          N, cast(b as time(0)) as b
    from (values (1, '08:00'),
                 (2, '17:00'),
                 (3, '23:30'),
                 (4, '04:00')
        ) as T(N, b)
) ---select * from B;
, Bh as (-- найдем разность между началом суток и началом 1-й смены в часах (h)
  select datepart(hh, b) as h  from B  where N=1 
) --select * from Bh
, BB as (-- переведем стрелки часов на h часов назад
  select N, cast(dateadd(hour, -(select h from Bh), b) as time(0)) as b   from B
)   -- select * from BB
, BL as (-- найдем разность между началом 1-й смены на началом всех других смен в минутах (L)
  select N, b, datediff(minute, '00:00', b) as L from BB
) --   select * from BL
, C as (
  select N, L
       , cast(dateadd(minute, L, '19000101') as datetime2(0)) as bb --начало смены как датавремя
   from BL
)  ---  select * from C;
, Cf as (
  select N, L, bb
       , dateadd(day, 1, (select bb from C where N=1)) as f  --начало 1-й смены следующего дня как датавремя
   from C
)  ---  select * from Cf;
, D as (
  select N, L
       , datediff(minute, bb, lead(bb, 1, f) over(order by N)) as T --длительность смены в минутах
    from Cf
)   ---  select * from D;
, E as (-- найдем начала реальных смен
select cast(dt as date)        as dt
     , N
     , dateadd(minute, L, dt)  as smBeg
     , T
  from  (select dateadd(hh, (select h from Bh), dt) as dt  from A) a  -- вернем стрелку часов на место
  cross join D
) ---  select * from E

select dt                                            as [дата]
     , N                                             as [смена]
     , smBeg                                         as [начало смены]
     , dateadd(minute, T, smBeg)                     as [конец смены]
     , T                                             as Tm
     , cast(dateadd(minute, T, '00:00') as time(0))  as Thms
  from E
 order by smBeg;

Результат:

Код: plaintext

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

дата		смена	начало смены		конец смены		Tm	Thms
2022-01-20	1	2022-01-20 08:00:00	2022-01-20 17:00:00	540	09:00:00
2022-01-20	2	2022-01-20 17:00:00	2022-01-20 23:30:00	390	06:30:00
2022-01-20	3	2022-01-20 23:30:00	2022-01-21 04:00:00	270	04:30:00
2022-01-20	4	2022-01-21 04:00:00	2022-01-21 08:00:00	240	04:00:00

2022-01-21	1	2022-01-21 08:00:00	2022-01-21 17:00:00	540	09:00:00
2022-01-21	2	2022-01-21 17:00:00	2022-01-21 23:30:00	390	06:30:00
2022-01-21	3	2022-01-21 23:30:00	2022-01-22 04:00:00	270	04:30:00
2022-01-21	4	2022-01-22 04:00:00	2022-01-22 08:00:00	240	04:00:00

2022-01-22	1	2022-01-22 08:00:00	2022-01-22 17:00:00	540	09:00:00
2022-01-22	2	2022-01-22 17:00:00	2022-01-22 23:30:00	390	06:30:00
2022-01-22	3	2022-01-22 23:30:00	2022-01-23 04:00:00	270	04:30:00
2022-01-22	4	2022-01-23 04:00:00	2022-01-23 08:00:00	240	04:00:00

Видим, что 3-я смена заканчивается на следующие сутки, а 4-я смена начинается и заканчивается на следующие сутки.

...

Рейтинг:

0 / 0

01.02.2022, 17:44

| Ответить | Цитировать | Написать

12 сообщений из 12, страница 1 из 1

Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Увеличение времени на разные промежутки

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=46&fpage=4&tid=1683880]:	0ms
get settings:	11ms
get forum list:	16ms
check forum access:	4ms
check topic access:	4ms
track hit:	46ms
get topic data:	14ms
get forum data:	3ms
get page messages:	57ms
get tp. blocked users:	2ms
others:	238ms

total:	395ms