ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Кол-во часов, отработанное бригадой за период
25 сообщений из 31, страница 1 из 2
  1  все
Кол-во часов, отработанное бригадой за период
    #39590533
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Добрый день, уважаемые форумчане!
Помогите новичку!
Есть таблица учета времени работы бригады, в которой каждый вид работ учитывается отдельной строкой

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
-- creating table
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDate date NOT NULL,
FromTime time NOT NULL,
ToDate date NOT NULL,
ToTime time NOT NULL
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDate,FromTime,ToDate,ToTime)
VALUES
('Покраска стен','2017-09-10','20:00','2017-09-11','06:00'),
('Установка дверей','2017-09-10','18:00','2017-09-10','22:00'),
('Поклейка обоев','2017-09-11','04:00','2017-09-11','9:00'),
('Установка окон','2017-09-12','14:00','2017-09-12','20:00'),
('Покраска полов','2017-09-12','16:00','2017-09-13','04:00')



Нужно посчитать общее кол-во часов, отработанное бригадой (то есть в данном примере результатом выборки должно стать кол-во часов 31, см вложение для нагладности)
Подскажите плиз, в какую сторону копать?
Заранее благодарен!
...
Рейтинг: 0 / 0
25.01.2018, 02:19
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590535
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Вложение не вложилось)
...
Рейтинг: 0 / 0
25.01.2018, 02:20
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590538
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Гордон Шамуэй,

найти разность каждого этупа работ, после чего - посчитать сумму разностей.
читать про sum + group by
и про арифметику дат...
...
Рейтинг: 0 / 0
25.01.2018, 05:31
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590540
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Добрый Э - Эх,

Интервалы пересекаются, так что простая арифметика не прокатит.
По теме подойдёт: Добавить недостающие периоды .
...
Рейтинг: 0 / 0
25.01.2018, 05:49
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590543
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Kopelly,

опять всё усложняешь..... зачем раскладывать диапазоны в точки, точки собирать в диапазоны, если можно сразу собрать в один покрывающий диапазон кучу пересекающихся?
...
Рейтинг: 0 / 0
25.01.2018, 06:09
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590545
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Гордон Шамуэй,

ты бы ещё версию сервера огласил, чтобы понимать какие решения у тебя будут работать....
...
Рейтинг: 0 / 0
25.01.2018, 06:14
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590559
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Гордон Шамуэй,

ладно, будем считать,что у тебя 2012 и выше....

для упрощения демонстрации ВОЗМОЖНОГО варианта решения я внес небольшие изменения в структуру исходной таблицы. Раздельные поля DATE и TIME объединил в целостный DateTime. Но это можно было бы сделать и на уровне запроса, соответствующими преобразованиями, и далее к преобразованным данным применить предложенный запрос...

Код: 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.
-- creating table
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDateTime datetime NOT NULL,
ToDateTime datetime NOT NULL
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDateTime,ToDateTime)
VALUES
(   'Покраска стен', '2017-09-10 20:00:00', '2017-09-11 06:00:00'),
('Установка дверей', '2017-09-10 18:00:00', '2017-09-10 22:00:00'),
(  'Поклейка обоев', '2017-09-11 04:00:00', '2017-09-11 09:00:00'),
(  'Установка окон', '2017-09-12 14:00:00', '2017-09-12 20:00:00'),
(  'Покраска полов', '2017-09-12 16:00:00', '2017-09-13 04:00:00')

--
-- Основной запрос:
select distinct sum(DATEDIFF(hour, min(FromDateTime), dateadd(hh,1,max(ToDateTime)))) over() as x_value
  from (
         select  FromDateTime, ToDateTime, sum(sog) over(order by FromDateTime,ToDateTime) as grp_id
           from (
                  select FromDateTime, ToDateTime,
                         case 
                           when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog 
                    from uchet_vremeni t
                ) v0
       ) v1
 group by grp_id

--
-- Результат запроса:

x_value
-------
     31

On-line проверка на сайте sqlfiddle.com
...
Рейтинг: 0 / 0
25.01.2018, 07:23
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590564
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Добрый Э - ЭхГордон Шамуэй,

ладно, будем считать,что у тебя 2012 и выше....

для упрощения демонстрации ВОЗМОЖНОГО варианта решения я внес небольшие изменения в структуру исходной таблицы .
Раздельные поля DATE и TIME объединил в целостный DateTime.
Но это можно было бы сделать и на уровне запроса, соответствующими преобразованиями, и далее к преобразованным данным применить предложенный запрос...

Код: 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.
-- creating table
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDateTime datetime NOT NULL, -- Внесенные изменения в структуру таблицы
ToDateTime datetime NOT NULL -- Внесенные изменения в структуру таблицы
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDateTime,ToDateTime)
VALUES
(   'Покраска стен', '2017-09-10 20:00:00', '2017-09-11 06:00:00'),
('Установка дверей', '2017-09-10 18:00:00', '2017-09-10 22:00:00'),
(  'Поклейка обоев', '2017-09-11 04:00:00', '2017-09-11 09:00:00'),
(  'Установка окон', '2017-09-12 14:00:00', '2017-09-12 20:00:00'),
(  'Покраска полов', '2017-09-12 16:00:00', '2017-09-13 04:00:00')

--
-- Основной запрос:
select distinct sum(DATEDIFF(hour, min(FromDateTime), dateadd(hh,1,max(ToDateTime)))) over() as x_value
  from (
         select  FromDateTime, ToDateTime, sum(sog) over(order by FromDateTime,ToDateTime) as grp_id
           from (
                  select FromDateTime, ToDateTime,
                         case 
                           when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog 
                    from uchet_vremeni t
                ) v0
       ) v1
 group by grp_id

--
-- Результат запроса:

x_value
-------
     31

On-line проверка на сайте sqlfiddle.com Чтобы было понятнее, о чем я.... :)
...
Рейтинг: 0 / 0
25.01.2018, 07:29
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590705
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
На 2008 не могу придумать, как сделать короче...
 
Просьба убрать от экранов детей, кошек, беременных и кормящих женщин, и других эмоционально нестабильных личностей
Код: 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.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
IF OBJECT_ID( 'tempdb..#worksheet' ) IS NOT NULL
  DROP TABLE #worksheet
;
CREATE TABLE
  #worksheet (
    [Team] VARCHAR(20) NOT NULL,
    [Job] VARCHAR(50) NOT NULL,
    [FromDateTime] DATETIME2(0) NOT NULL,
    [TillDateTime] DATETIME2(0) NOT NULL,
    PRIMARY KEY ( [Team], [Job], [FromDateTime] ) )
;
-- inserting test data
INSERT INTO
  #worksheet (
    [Team],
    [Job],
    [FromDateTime],
    [TillDateTime] )
VALUES
  ( 'Бригада1', 'Покраска стен',    '2017-09-10 20:00:00', '2017-09-11 06:00:00' ),
  ( 'Бригада1', 'Установка дверей', '2017-09-10 18:00:00', '2017-09-10 22:00:00' ),
  ( 'Бригада1', 'Поклейка обоев',   '2017-09-11 04:00:00', '2017-09-11 09:00:00' ),
  ( 'Бригада1', 'Установка окон',   '2017-09-12 14:00:00', '2017-09-12 20:00:00' ),
  ( 'Бригада1', 'Покраска полов',   '2017-09-12 16:00:00', '2017-09-13 04:00:00' )
;
IF OBJECT_ID( 'tempdb..#worksheet_plain' ) IS NOT NULL
  DROP TABLE #worksheet_plain
;
SELECT 
  [Team], 
  [Date] = [FromDateTime],
  [Direction] = 1
INTO
  #worksheet_plain
FROM
  #worksheet
UNION ALL
SELECT 
  [Team], 
  [Date] = [TillDateTime],
  [Direction] = -1
FROM
  #worksheet
;
WITH
t0 AS (
  SELECT
    ws.[Team],
    ws.[Date],
    [IsEnd] = CASE WHEN ws.[Direction] + ISNULL( SUM( wsp.[Direction] ), 0 ) = 0 THEN 1 ELSE 0 END
  FROM
    #worksheet_plain ws
    LEFT JOIN #worksheet_plain wsp ON (
          wsp.[Team] = ws.[Team]
      AND wsp.[Date] < ws.[Date] )
  GROUP BY
    ws.[Team],
    ws.[Date],
    ws.[Direction]
),
t1 AS (
  SELECT
    [Team],
    [Date],
    [IsEnd],
    [gn] = ROW_NUMBER() OVER ( PARTITION BY [Team] ORDER BY [Date] )
         - ROW_NUMBER() OVER ( PARTITION BY [Team], [IsEnd] ORDER BY [Date] )
  FROM
    t0
),
t2 AS (
  SELECT
    [Team],
    [Date] = MIN( [Date] ),
    [IsEnd]
  FROM
    t1
  GROUP BY
    [Team],
    [IsEnd],
    [gn]
)
SELECT
  te.[Team],
  [FromDateTime] = MAX( ts.[Date] ),
  [TillDateTime] = te.[Date]
FROM
  t2 te
  INNER JOIN t2 ts ON (
        ts.[Team] = te.[Team]
    AND ts.[Date] < te.[Date] )
WHERE
  te.[IsEnd] = 1
GROUP BY
  te.[Team],
  te.[Date]
ORDER BY
  1, 2

...
Рейтинг: 0 / 0
25.01.2018, 11:11
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590712
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
Фикс
 
Презерватив на свечку
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SELECT
  te.[Team],
  [FromDateTime] = MAX( ts.[Date] ),
  [TillDateTime] = te.[Date]
FROM
  t2 te
  INNER JOIN t2 ts ON (
        ts.[Team] = te.[Team]
     AND ts.[Date] < te.[Date]
     AND ts.[IsEnd] = 0 )
WHERE
  te.[IsEnd] = 1
GROUP BY
  te.[Team],
  te.[Date]
ORDER BY
  1, 2

...
Рейтинг: 0 / 0
25.01.2018, 11:18
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590767
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Руслан ДамировичНа 2008 не могу придумать, как сделать короче...

Код: 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.
-- creating table
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDateTime datetime NOT NULL, -- Внесенные изменения в структуру таблицы
ToDateTime datetime NOT NULL -- Внесенные изменения в структуру таблицы
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDateTime,ToDateTime)
VALUES
(   'Покраска стен', '2017-09-10 20:00:00', '2017-09-11 06:00:00'),
('Установка дверей', '2017-09-10 18:00:00', '2017-09-10 22:00:00'),
(  'Поклейка обоев', '2017-09-11 04:00:00', '2017-09-11 09:00:00'),
(  'Установка окон', '2017-09-12 14:00:00', '2017-09-12 20:00:00'),
(  'Покраска полов', '2017-09-12 16:00:00', '2017-09-13 04:00:00')

--
-- Основной запрос:
with
  b$m_t as
    (
      select distinct v.p, row_number() over(order by p) as rn
        from uchet_vremeni t
       cross apply(values (fromdatetime), (dateadd(hh,1,Todatetime))) v(p)
    )
-- 
select sum(datediff(hh, t1.p, t2.p)) as x_value
  from b$m_t t1
  join b$m_t t2
    on t1.rn = t2.rn - 1
 where exists
        (
          select null from uchet_vremeni t0
           where t2.p > t0.fromdatetime
             and t0.Todatetime > t1.p
        )

--
-- Результат запроса:

x_value
-------
     31

On-line проверка на сайте sqlfiddle.com

Но возможно - магия данных. Нужно проверять на рассширенном составе тестовых данных....
...
Рейтинг: 0 / 0
25.01.2018, 11:53
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590783
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Добрый Э - ЭхРуслан ДамировичНа 2008 не могу придумать, как сделать короче...

Код: 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.
-- creating table
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDateTime datetime NOT NULL, -- Внесенные изменения в структуру таблицы
ToDateTime datetime NOT NULL -- Внесенные изменения в структуру таблицы
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDateTime,ToDateTime)
VALUES
(   'Покраска стен', '2017-09-10 20:00:00', '2017-09-11 06:00:00'),
('Установка дверей', '2017-09-10 18:00:00', '2017-09-10 22:00:00'),
(  'Поклейка обоев', '2017-09-11 04:00:00', '2017-09-11 09:00:00'),
(  'Установка окон', '2017-09-12 14:00:00', '2017-09-12 20:00:00'),
(  'Покраска полов', '2017-09-12 16:00:00', '2017-09-13 04:00:00')

--
-- Основной запрос:
with
  b$m_t as
    (
      select distinct v.p, DENSE_RANK() over(order by p) as rn
        from uchet_vremeni t
       cross apply(values (fromdatetime), (dateadd(hh,1,Todatetime))) v(p)
    )
-- 
select sum(datediff(hh, t1.p, t2.p)) as x_value
  from b$m_t t1
  join b$m_t t2
    on t1.rn = t2.rn - 1
 where exists
        (
          select null from uchet_vremeni t0
           where t2.p > t0.fromdatetime
             and t0.Todatetime > t1.p
        )

--
-- Результат запроса:

x_value
-------
     31

On-line проверка на сайте sqlfiddle.com

Но возможно - магия данных. Нужно проверять на рассширенном составе тестовых данных....
row_number заменить на dense_rank, конечно же....
...
Рейтинг: 0 / 0
25.01.2018, 12:00
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39590873
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Добрый Э - Эх,
При часовом интервале (датавремя начала = датавремя окончания) без пересечений с другими интервалами час работы пропадает:

Код: 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.
drop TABLE uchet_vremeni
CREATE TABLE uchet_vremeni
(
Id int IDENTITY NOT NULL PRIMARY KEY,
[Job] nvarchar(50) NOT NULL,
FromDateTime datetime NOT NULL, -- Внесенные изменения в структуру таблицы
ToDateTime datetime NOT NULL -- Внесенные изменения в структуру таблицы
)
-- inserting test data
INSERT INTO uchet_vremeni
([Job],FromDateTime,ToDateTime)
VALUES
[color=yellow]('Установка дверей', '2017-09-10 18:00:00', '2017-09-10 18:00:00'), -- Часовая работа[/color]
(   'Покраска стен', '2017-09-10 19:00:00', '2017-09-11 06:00:00'), --Продолжение без перерыва
(  'Поклейка обоев', '2017-09-11 04:00:00', '2017-09-11 09:00:00'), --Далее по тексту
(  'Установка окон', '2017-09-12 14:00:00', '2017-09-12 20:00:00'),
(  'Покраска полов', '2017-09-12 16:00:00', '2017-09-13 04:00:00');

--
-- Основной запрос:
with
  b$m_t as
    (
      select distinct v.p, DENSE_RANK() over(order by p) as rn
        from uchet_vremeni t
       cross apply(values (fromdatetime),  (dateadd(hh,1,Todatetime))) v(p)
    )
select sum(datediff(hh, t1.p, t2.p)) as x_value
  from b$m_t t1
  join b$m_t t2
    on t1.rn = t2.rn - 1
 where exists
        (
          select null from uchet_vremeni t0
           where t2.p > t0.fromdatetime
[color=yellow]             and dateadd(ms,-3,dateadd(hh,1,t0.Todatetime)) >  t1.p --Изменил условие[/color]
--and t0.Todatetime >  t1.p - старое условие
        )
...
Рейтинг: 0 / 0
25.01.2018, 13:15
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591178
Руслан Дамирович
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Руслан Дамирович
Участник
KopellyПри часовом интервале (датавремя начала = датавремя окончания) без пересечений с другими интервалами час работы пропадает
- Видишь час работы?
- Не-е-ет.
- И я нет. А он есть!
...
Рейтинг: 0 / 0
25.01.2018, 18:14
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591384
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Добрый Э - Эх,

Спасибо огромное вам и остальным участникам!
Да, у меня 2012

Не до конца понимаю как оно работает, даже глядя на готовый код(

Код: sql
1.
dateadd(hh,1,max(ToDateTime)))) over()

- что означает вот это прибавление одного часа например?

и вот это тоже:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
case 
                 when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog
...
Рейтинг: 0 / 0
26.01.2018, 01:17
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591385
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Добрый Э - Эх,

Чтобы не вносить изменения в структуру таблицы, сделал это в виде представления:

Код: sql
1.
2.
3.
WITH Temp AS
(SELECT JOB, CAST (FromDate AS datetime)+CAST (FromTime AS datetime) AS [Begin], CAST (ToDate AS datetime)+CAST (ToTime AS datetime) AS [End]   FROM uchet_vremeni )
SELECT * FROM TEMP
...
Рейтинг: 0 / 0
26.01.2018, 01:21
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591387
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Добрый Э - Эх,

Если я правильно понял, нужно погрузиться в изучение оконных функций.
Пока что это для меня темный лес(
...
Рейтинг: 0 / 0
26.01.2018, 01:31
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591398
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Гордон ШамуэйНе до конца понимаю как оно работает, даже глядя на готовый код(

Код: sql
1.
dateadd(hh,1,max(ToDateTime)))) over()

- что означает вот это прибавление одного часа например?

Исходя из твоего второго поста: час окончания работы засчитывается полностью - то есть работа с 9-00 до 10-00 - это два часа работы. А работа с 9-00 до 9-00 - один час работы.

Гордон Шамуэйи вот это тоже:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
case 
                 when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog


Если одна из предыдущих работ окончилась после начала текущей, то ставим 0, иначе 1.
Это позволяет проставить 1 для тех работ которые не имеют пересечения с более ранними работами.

Потом просуммировав этот флаг по предыдущим работам:
Код: sql
1.
sum(sog) over(order by FromDateTime,ToDateTime) as grp_id


получим группы интервалов которые пересекаются внутри группы, но пересекаются с другими группами. Взяв максимум и минимум по таким группам получим начала и конец непрерывной работы.
...
Рейтинг: 0 / 0
26.01.2018, 04:28
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591399
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Гордон ШамуэйДобрый Э - Эх,

Если я правильно понял, нужно погрузиться в изучение оконных функций.
Пока что это для меня темный лес(
Можно и без них, но с ними может быть удобнее писать и значительнее быстрее выполняться.
...
Рейтинг: 0 / 0
26.01.2018, 04:33
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591400
Kopelly
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Kopelly
Участник
Ошибочка:
Kopelly получим группы интервалов которые пересекаются внутри группы, но не пересекаются с другими группами. Взяв максимум и минимум по таким группам получим начала и конец непрерывной работы.
...
Рейтинг: 0 / 0
26.01.2018, 04:34
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591405
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
KopellyГордон ШамуэйНе до конца понимаю как оно работает, даже глядя на готовый код(

Код: sql
1.
dateadd(hh,1,max(ToDateTime)))) over()

- что означает вот это прибавление одного часа например?

Исходя из твоего второго поста: час окончания работы засчитывается полностью - то есть работа с 9-00 до 10-00 - это два часа работы. А работа с 9-00 до 9-00 - один час работы.Совершенно верно. У автора каждое значение, по сути - это интервал времени длиной в час. MS SQL server же каждое значение воспринимает как точку. Соответственно, для правильного перевода арифметики дат MS SQL Server-а в арифметику дат топикстартера нужно прибавлять к дате завершения один час. Как вариант, можно было брать datediff между исходными значениями и к результату его работы добавлять единицу:
Код: sql
1.
2.
3.
4.
-- вполне можно заменить конструкцию:
sum(DATEDIFF(hour, min(FromDateTime), dateadd(hh,1,max(ToDateTime)))) -- выделенный красной областью текст - выкинуть
-- на такое:
sum(DATEDIFF(hour, min(FromDateTime), max(ToDateTime)) + 1) -- не забыть добавить единицу к разности дат...




Гордон Шамуэйи вот это тоже:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
case 
                 when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog

Этот кусок года помечает все начала непересекающихся групп... Если вспомнить классическое решение Ицика Бен-Гана задачи группировки отрезков , то этот фрагмент кода по логике своей работы эквивалентен поиску всех начал (представление vw_dt_start в статье из ссылки)...
...
Рейтинг: 0 / 0
26.01.2018, 05:54
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591406
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Гордон ШамуэйДобрый Э - Эх,

Если я правильно понял, нужно погрузиться в изучение оконных функций.
Пока что это для меня темный лес(Возможно, прочтение этой книжки позволит научиться ориентироваться в этом лесу....
...
Рейтинг: 0 / 0
26.01.2018, 06:04
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591880
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Kopelly,
Большое спасибо!
Буду разбираться.
...
Рейтинг: 0 / 0
26.01.2018, 18:39
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39591882
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Добрый Э - Эх,

Уже посмотрел пару видеоуроков по теме.
За книжку спасибо, обязательно найду!
...
Рейтинг: 0 / 0
26.01.2018, 18:40
| Ответить | Цитировать | Написать  
Кол-во часов, отработанное бригадой за период
    #39592041
Гордон Шамуэй
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гордон Шамуэй
Гость
Kopelly,

авторИсходя из твоего второго поста: час окончания работы засчитывается полностью - то есть работа с 9-00 до 10-00 - это два часа работы. А работа с 9-00 до 9-00 - один час работы.

Ребята, понял свою исходную ошибку.
Конечно же, интервал, например, от 18 до 22 часов - это 4 часа, а не 5.
То есть в итоге должно получиться 29 часов, а не 31.
Исходя из этого, вот этот кусок года нужно исключить
Код: sql
1.
dateadd(hh,0,



И остается вот так?

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select distinct sum(DATEDIFF(hour, min(FromDateTime), max(ToDateTime))) over() as x_value
  from (
         select  FromDateTime, ToDateTime, sum(sog) over(order by FromDateTime,ToDateTime) as grp_id
           from (
                  select FromDateTime, ToDateTime,
                         case 
                           when max(ToDateTime) 
                               over(order by FromDateTime, ToDateTime 
                                        rows between unbounded preceding
                                                         and 1 preceding
                                   ) >= FromDateTime
                           then 0 
                           else 1 
                         end as sog 
                    from brigada t
                ) v0
       ) v1
 group by grp_id



Тогда запрос выдает результат 29
...
Рейтинг: 0 / 0
27.01.2018, 01:35
| Ответить | Цитировать | Написать  
25 сообщений из 31, страница 1 из 2
  1  все
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Кол-во часов, отработанное бригадой за период
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]