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

Откуда:
Сообщений: 19
Добрый день, имеется таблица
CREATE TABLE [dbo].[УРВ] (
    [ИдУРВ]        INT            IDENTITY (1, 1) NOT NULL,
    [ИдСотрудника] INT            NULL,
    [ДатаВремя]    SMALLDATETIME  NULL,
    [Действие]     NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([ИдУРВ] ASC),
    CONSTRAINT [FK_УРВ_Сотрудники] FOREIGN KEY ([ИдСотрудника]) REFERENCES [dbo].[Сотрудники] ([ИдСотрудника])
);

Каждый день сотрудники при входе и выходе с работы отмечаются, в [Действие] пишется "Начало рабочего дня" и "Конец рабочего дня".
Знаю есть DATEDIFF, но как определить время работы за один день, для определенного сотрудника знаю, но как составить таблицу со списком сотрудников и количеством отработанного времени за 1 день и за 1 неделю?
24 апр 21, 15:30    [22313604]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
Если у вас все аккуратно - в каждый день только два действия "Начало рабочего дня" и "Конец рабочего дня" причем
"Начало рабочего дня" < "Конец рабочего дня"
тогда в лоб
select [ИдСотрудника], [ДатаВремя] as d_start, 
	(select max([ДатаВремя]) from [dbo].[УРВ] b
	  where a.[ИдСотрудника]=b.[ИдСотрудника]
		and b.[Действие]='Конец рабочего дня'
		and cast(a.[ДатаВремя] as date)<= b.[ДатаВремя]
		and b.[ДатаВремя]<dateadd(day,1,cast(a.[ДатаВремя] as date))
	) as d_end
from [dbo].[УРВ] a
where [Действие]='Начало рабочего дня'

Данный запрос будет правильно работать только на правильных данных.
В случае бардака в данных он вернет херню

Сообщение было отредактировано: 24 апр 21, 21:38
24 апр 21, 21:45    [22313738]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
SERG1257, Спасибо! Только мне надо было найти разницу во времени, т.е сколько сотрудник провел времени на роботе.
Как быть если в [Действие] хранится не только начало и конец рабочего дня?

Сообщение было отредактировано: 24 апр 21, 22:01
24 апр 21, 22:03    [22313746]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
SERG1257, Спасибо! Только мне надо было найти разницу во времени, т.е сколько сотрудник провел времени на роботе.
Как быть если в [Действие] хранится не только начало и конец рабочего дня?

Сообщение было отредактировано: 24 апр 21, 22:01
24 апр 21, 22:06    [22313747]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
godsql
Member

Откуда:
Сообщений: 198
Kaktyc007,
просто пример, отражающий последовательность действий для одного человека
Принимаем, что есть обязательное 1-е событие "вход", потом, 2-е, соответственно, "выход"
;with #t as(
select 1 as id, getdate() as dotime
union all
select id+1, dateadd(HH, 4, dotime) from #t
where id <20
), 
#t2 as (
select 
id, 
dotime, 
lead(dotime) over(order by (select 1/0)) as ndotime, 
datediff(hh, dotime, lead(dotime) over(order by (select 1/0)) ) as dtime   
from #t
),
#t3 as (
select id, dtime from  #t2
where id %2 <> 0
)
select sum(dtime) as worktime from #t3
24 апр 21, 22:40    [22313764]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
Kaktyc007
Как быть если в [Действие] хранится не только начало и конец рабочего дня?
Приведите пример, написав скрипты для заполнения данными таблицы [dbo].[УРВ]

Что должен показать отчет в случае некорректных данных (Начал работу в 23:00 вечера, закончил в два ночи)
Зашел на работу в 8:00 а выхода в 17:00 нет, следующая запись начало работы в 8 следующего дня
И наоборот имеем несколько записей "Начало рабочего дня" в 8:00, 9:00 и 10:00
Или несколько записей "Конец рабочего дня" в 17:00, 18:00 и 19:00

Вы можете быть свято уверенны, что такого не будет, потому что нибудет никогда, но ответ - что должен показать отчет для таких данных должен быть приготовлен.
25 апр 21, 02:20    [22313812]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
SERG1257
Приведите пример, написав скрипты для заполнения данными таблицы [dbo].[УРВ]


insert into УРВ (ИдСотрудника, ДатаВремя, Действие) values ('6', '2021-04-25 08:00:00', N'Начало рабочего дня')
insert into УРВ (ИдСотрудника, ДатаВремя, Действие) values ('6', '2021-04-25 12:00:00', N'Обед')
insert into УРВ (ИдСотрудника, ДатаВремя, Действие) values ('6', '2021-04-25 17:00:00', N'Конец рабочего дня')


SERG1257
Что должен показать отчет в случае некорректных данных (Начал работу в 23:00 вечера, закончил в два ночи)
Зашел на работу в 8:00 а выхода в 17:00 нет, следующая запись начало работы в 8 следующего дня
И наоборот имеем несколько записей "Начало рабочего дня" в 8:00, 9:00 и 10:00
Или несколько записей "Конец рабочего дня" в 17:00, 18:00 и 19:00

В первом случае 3 часа. А в остальных случаях не допускать этого.
25 апр 21, 11:02    [22313864]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
Вот пример для вычисления длительности рабочего дня в минутах для одного сотрудника с одной записью в таблице, если добавить еще одно появления на работе, уже ругается: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
SELECT ИдСотрудника, DATEDIFF (MINUTE, (select ДатаВремя from УРВ where Действие=N'Начало рабочего дня' and ИдСотрудника=5 ), (select ДатаВремя from УРВ where Действие=N'Конец рабочего дня' and ИдСотрудника=5)) 
from УРВ where ИдСотрудника=5 and Действие=N'Начало рабочего дня'
25 апр 21, 11:18    [22313868]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
aleks222
Member

Откуда:
Сообщений: 1352
Kaktyc007
Вот пример для вычисления длительности рабочего дня в минутах для одного сотрудника с одной записью в таблице, если добавить еще одно появления на работе, уже ругается: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
SELECT ИдСотрудника, DATEDIFF (MINUTE, (select ДатаВремя from УРВ where Действие=N'Начало рабочего дня' and ИдСотрудника=5 ), (select ДатаВремя from УРВ where Действие=N'Конец рабочего дня' and ИдСотрудника=5)) 
from УРВ where ИдСотрудника=5 and Действие=N'Начало рабочего дня'


Вот в чем проблема "настоящих программиздов"?
Арифметику они не учили - вот в чем их проблема.

1. Если в табличке "все хорошо", т.е. каждому началу соответствует каждый конец

2.
declare @now datetime = getdate();
SELECT ИдСотрудника
          , ВремяРаботыМинуты = sum( case Действие when N'Начало рабочего дня' then - datediff(minute, ДатаВремя, @now)
                                                   when N'Конец рабочего дня' then + datediff(minute, ДатаВремя, @now)
                                                  else 0
                                     end
                                  )
from УРВ 
group by ИдСотрудника


3. Если же там "не фсе хорошо" - надо сначала решить эту проблему...

Сообщение было отредактировано: 25 апр 21, 12:08
25 апр 21, 12:10    [22313879]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
aleks222
Kaktyc007
Вот пример для вычисления длительности рабочего дня в минутах для одного сотрудника с одной записью в таблице, если добавить еще одно появления на работе, уже ругается: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
SELECT ИдСотрудника, DATEDIFF (MINUTE, (select ДатаВремя from УРВ where Действие=N'Начало рабочего дня' and ИдСотрудника=5 ), (select ДатаВремя from УРВ where Действие=N'Конец рабочего дня' and ИдСотрудника=5)) 
from УРВ where ИдСотрудника=5 and Действие=N'Начало рабочего дня'


Вот в чем проблема "настоящих программиздов"?
Арифметику они не учили - вот в чем их проблема.

1. Если в табличке "все хорошо", т.е. каждому началу соответствует каждый конец

2.
declare @now datetime = getdate();
SELECT ИдСотрудника
          , ВремяРаботыМинуты = sum( case Действие when N'Начало рабочего дня' then - datediff(minute, ДатаВремя, @now)
                                                   when N'Конец рабочего дня' then + datediff(minute, ДатаВремя, @now)
                                                  else 0
                                     end
                                  )
from УРВ 
group by ИдСотрудника


3. Если же там "не фсе хорошо" - надо сначала решить эту проблему...

Спасибо, но зачем брать текущее системное время? И как вывести отработанное время за неделю, а не за все время?
25 апр 21, 12:18    [22313884]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
aleks222
Member

Откуда:
Сообщений: 1352
Kaktyc007
aleks222
пропущено...


Вот в чем проблема "настоящих программиздов"?
Арифметику они не учили - вот в чем их проблема.

1. Если в табличке "все хорошо", т.е. каждому началу соответствует каждый конец

2.
declare @now datetime = getdate();
SELECT ИдСотрудника
          , ВремяРаботыМинуты = sum( case Действие when N'Начало рабочего дня' then - datediff(minute, ДатаВремя, @now)
                                                   when N'Конец рабочего дня' then + datediff(minute, ДатаВремя, @now)
                                                  else 0
                                     end
                                  )
from УРВ 
group by ИдСотрудника


3. Если же там "не фсе хорошо" - надо сначала решить эту проблему...

Спасибо, но 1) зачем брать текущее системное время? И 2)как вывести отработанное время за неделю, а не за все время?


1. Ну... арифметика - полезная наука. Учи.
2. Ваще-то, оно выводит "за все время, что есть в таблице". Т.е. если в таблице оставить неделю - будет за неделю. "Оставить" = наложить нужный фильтр или добавить группировку по неделям.
25 апр 21, 12:23    [22313888]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
aleks222, а арифметика тут причем? если datediff находит разницу между записанным и текущим временем
25 апр 21, 13:03    [22313901]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
godsql
Member

Откуда:
Сообщений: 198
Kaktyc007
aleks222, а арифметика тут причем? если datediff находит разницу между записанным и текущим временем

Если вычесть Разницу между "концом" и "текущим" из Разницы между "началом" и "текущим" - это и будет разница между "началом" и "концом".
в-а = (1000+в) - (1000+а)

Сообщение было отредактировано: 25 апр 21, 13:40
25 апр 21, 13:45    [22313910]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
godsql
Kaktyc007
aleks222, а арифметика тут причем? если datediff находит разницу между записанным и текущим временем

Если вычесть Разницу между "концом" и "текущим" из Разницы между "началом" и "текущим" - это и будет разница между "началом" и "концом".
в-а = (1000+в) - (1000+а)

почему нельзя сразу вычесть разницу между концом и началом?
25 апр 21, 14:35    [22313916]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Kaktyc007
Member

Откуда:
Сообщений: 19
Как объединить эти два запроса в один?
select [ИдСотрудника], [ДатаВремя] as d_start, 
	(select max([ДатаВремя]) from [dbo].[УРВ] b
	  where a.[ИдСотрудника]=b.[ИдСотрудника]
		and b.[Действие]='Конец рабочего дня'
		and cast(a.[ДатаВремя] as date)<= b.[ДатаВремя]
		and b.[ДатаВремя]<dateadd(day,1,cast(a.[ДатаВремя] as date))
	) as d_end
from [dbo].[УРВ] a
where [Действие]='Начало рабочего дня'


declare @now datetime = getdate();
SELECT ИдСотрудника
          , ВремяРаботыМинуты = sum( case Действие when N'Начало рабочего дня' then - datediff(minute, ДатаВремя, @now)
                                                   when N'Конец рабочего дня' then + datediff(minute, ДатаВремя, @now)
                                                  else 0
                                     end
                                  )
from УРВ 
group by ИдСотрудника
25 апр 21, 15:06    [22313925]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
SERG1257
Member

Откуда:
Сообщений: 2844
Kaktyc007
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
правильно ругается. Выбирайте одно значение

Kaktyc007
В первом случае 3 часа.
То бишь эта ситуация вполне законна.
Kaktyc007
А в остальных случаях не допускать этого.
Про не допускать это триггеры и акты (отчет о кривых данных)
Определяем бизнес правило - датой конца работы считается ближайшая дата от начала работы (в тот же или последующие дни)
Предполагаем что кривых данных в таблице нет - мы выловили их ранее.
Пишем подзапрос возвращающий ближайшую дату конца работы

(select min([ДатаВремя]) from [dbo].[УРВ] b
	  where a.[ИдСотрудника]=b.[ИдСотрудника]
		and b.[Действие]='Конец рабочего дня'
		and a.[ДатаВремя]<b.[ДатаВремя]
) as d_end

обратите внимание на агрегатную функцию min - специально чтобы не было ошибки выше
Также можно сделать top 1

(select top 1 [ДатаВремя] from [dbo].[УРВ] b
	  where a.[ИдСотрудника]=b.[ИдСотрудника]
		and b.[Действие]='Конец рабочего дня'
		and a.[ДатаВремя]<b.[ДатаВремя]
order by [ДатаВремя] 
) as d_end


У настоящих программистов появится соблазн засунуть этот подзапрос в скалярную функцию, не делайте этого - это убъет производительность

Дальше фильтр по [dbo].[УРВ] на день или неделю, и сумму (в часах или минутах) разницы между d_start и d_end
select [ИдСотрудника],a.[ДатаВремя] as d_start, sum(datediff(MINUTE,d_start,d_end))
from [dbo].[УРВ] a
where [начало отчетного периода]<=a.[ДатаВремя] and a.[ДатаВремя]<[конец отчетного периода]
and a.[Действие]='Начало рабочего дня'
group by [ИдСотрудника]

Запрос специально неправильный (без подстановки d_start и d_end) чтобы служба медом не казалась
25 апр 21, 17:20    [22313980]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
aleks222
Member

Откуда:
Сообщений: 1352
SERG1257

У настоящих программистов появится соблазн засунуть этот подзапрос в скалярную функцию, не делайте этого - это убъет производительность


Настоящие программизды не пользуют подзапросы без крайней нужды.
В данном, конкретном случае никаких подзапросов не надо.
От слова совсем.
25 апр 21, 17:37    [22313986]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Wlr-l
Member

Откуда:
Сообщений: 539
Решение от алекса безусловно сильное, как и совет "арифметика - полезная наука. Учи."
Я решил последовать совету, чтобы освежить свои знания арифметики.

Очевидно, что в предложенном решении:
1. Для всех событий их ДатаВремя всегда будет предшествовать @now.
2. Точка 'Начало рабочего дня' будет находиться на оси времени левее точки 'Конец рабочего дня'.
3. От точки 'Начало рабочего дня' до точки @now пройдет больше времени, чем
от точки 'Конец рабочего дня' до точки @now.

Следовательно, значение -datediff(minute, ДатаВремя, @now) = -(@now - 'Начало рабочего дня') будет отрицательным и определять знак суммы.

Т.е. запрос вернет отрицательное значение.

Первой мыслью было взять @now меньше любого ДатаВремя, чтобы получить положительный результат!
Но ведь дан хороший совет, проверь арифметикой!

В этом случае
1. Для всех событий их ДатаВремя всегда будет позже @now.
2. Точка 'Начало рабочего дня' будет находиться на оси времени левее точки 'Конец рабочего дня'.
3. От точки @now до точки 'Конец рабочего дня' пройдет больше времени, чем
от точки @now до точки 'Начало рабочего дня'.

Теперь знак суммы будет определять выражение (@now - 'Конец рабочего дня'), которое так же будет отрицательным.

Т.е. и в этом случае запрос вернет отрицательное значение.

Здесь я вспомнил об абсолютном значении, но был же дан хороший совет!

Можно просто изменить порядок дат в datediff, но еще проще поменять знаки в операторе case.
27 апр 21, 13:57    [22315072]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
aleks222
Member

Откуда:
Сообщений: 1352
Wlr-l

1. Для всех событий их ДатаВремя всегда будет предшествовать @now.

Это абсолютно пофиг.
Можно использовать любую фиксированную точку на шкале времени.
Арифметику ты тоже не доучил.
27 апр 21, 17:50    [22315237]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Wlr-l
Member

Откуда:
Сообщений: 539
aleks222
Wlr-l

1. Для всех событий их ДатаВремя всегда будет предшествовать @now.

Это абсолютно пофиг.
Можно использовать любую фиксированную точку на шкале времени.
Арифметику ты тоже не доучил.


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

2. Действительно, можно использовать любую точку на шкале времени.
У тебя она описана так: declare @now datetime = getdate();

3. В твоем запросе получается, что если алекс пришел на работу в 08, а ушел в 10,
то он проработал -2 часа. Далее, если часовая ставка 1 руб., то он заработал -2 рубля.
Т.е. алекс, проработав в организации с 08 до 10, остался должен этой организации.

4. Я сказал, что твое решение сильное, по сравнению с другими предложенными решениями,
и предложил его чуть-чуть подправить, чтобы время работы было положительным числом.

5. Кроме арифметики есть еще и логика.
27 апр 21, 20:42    [22315336]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
982183
Member

Откуда: VL
Сообщений: 3386
Та система доступа, которую видел я, не позволит войти второй раз по одной и той же карточке.
Как и два раза выйти.
28 апр 21, 03:26    [22315388]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
aleks222
Member

Откуда:
Сообщений: 1352
Wlr-l

3. В твоем запросе получается, что если алекс пришел на работу в 08, а ушел в 10,
то он проработал -2 часа. Далее, если часовая ставка 1 руб., то он заработал -2 рубля.
Т.е. алекс, проработав в организации с 08 до 10, остался должен этой организации.

Не придирайся.
Это минус из кармана работодателя.
28 апр 21, 06:00    [22315393]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Wlr-l
Member

Откуда:
Сообщений: 539
Да я и не придираюсь.

Вспомнилась фраза классика МЛ: "Учиться арифметике настоящим образом".
Вот я и учусь арифметике уже третий раз (сам, с дочкой, теперь с внучкой).

Операция вычитания - это НЕ коммутативная операция.
Собственно, как и сложение чисел с разными знаками.

И эта не коммутативность может сыграть злую шутку.
28 апр 21, 12:02    [22315533]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
msLex
Member

Откуда:
Сообщений: 8928
Wlr-l
Собственно, как и сложение чисел с разными знаками.


Сложение чисел с разными знаками (и вообще всех действительных чисел), таки, коммутативная операция.
28 апр 21, 12:24    [22315553]     Ответить | Цитировать Сообщить модератору
 Re: Вычислить отработанное время  [new]
Wlr-l
Member

Откуда:
Сообщений: 539
Да, это так, конечно, сложение чисел коммутативно.

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

ТС, скорее всего, не понял красоту этого решения его задачи.
Согласитесь, что для решения этой задачи абсолютное большинство использовало бы подзапросы: большая часть сообщений в этом обсуждении свелась к нахождению для каждого "начала" своего "конца"!

Поэтому я скажу спасибо алексу за это его решение задачи ТС!
28 апр 21, 13:47    [22315628]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить