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

Откуда:
Сообщений: 33
Форумчане, добрый день. Подскажите, поясните, как можно реализовать рассчет разницы по двум таблицам (исходные данные в них представлены на картинках).
Для начала я попробовал реализовать это таким запросом
select a.day, a.time as came, b.time as go, DATEDIFF(hour,a.time, b.time) as hours
from tIn a, tOut b
where a.day = b.day


вроде бы все ок вывелось, но столкнулся с такой проблемой.
Встречаются иногда записи, где человек пришел на работу, например, 20.08 в 10:33, а ушел 21.08 в 01:20, а потом опять 21.08 пришел в 11:00 и ушел в 18:00..

загрузка данных в таблицы времени прихода и ухода сделана так у меня
--ЗАГРУЗКА ДАННЫХ В ТАБЛИЦУ ВРЕМЕНИ ПРИХОДА
delete from tIn
insert into skud.dbo.tIn
select	FIO,
		location,
		CONVERT(date,day,105) as day, 
		min(CONVERT(time,time,108)) as time 
from skud.dbo.tInput
where location = 'Здание'
group by FIO,location,day



--ЗАГРУЗКА ДАННЫХ В ТАБЛИЦУ ВРЕМЕНИ УХОДА
delete from tOut
insert into skud.dbo.tOut
select	FIO,
		location,
		CONVERT(date,day,105) as day, 
		max(CONVERT(time,time,108)) as time 
from skud.dbo.tInput
where location = 'Неконтролируемая территория'
group by FIO,location,day

и при таком раскладе я потеряю время ухода сотрудника 21.08 в 01:20..не подскажете, как можно этого избежать?
и после этого еще рассчитать, вдобавок, все время нахождения на рабочем месте..т.е с 20.08 10:33 по 21.08 01:20 и потом еще с 21.08 11:00 по 21.08 18:00?
11 сен 14, 15:39    [16564500]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
k_mak,
Зачем Вы разделили date и time, которые должны быть представлены как одно целое, тогда у Вас все получиться

DATEDIFF(hour, a.datetime, b.datetime)
11 сен 14, 15:46    [16564567]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
k_mak
Member

Откуда:
Сообщений: 33
Wlr-l,
начальные данные имеют такой вид(см. скрин)
в sql они грузятся у меня через openquery из xls файла.

или Вы имеете ввиду на моменте загрузки в таблицу sql объединить их в одно поле?
а как тогда мне это спасет от проблемы "вылавливания ночного сотрудника"? ))

и в таком случае будет-ли корректно отсеивать время
min(a.datetime) as time 
..
и
..
max(b.datetime) as time 

?

К сообщению приложен файл. Размер - 6Kb
11 сен 14, 16:13    [16564751]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
Wlr-l
Member

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

1.Да, в момент загрузки можно объединить эти два столбика в один с типом данных datetime. В этом случае Вы всегда получите правильную разность времени ухода и времени прихода, т.е. время нахождения сотрудника на работе.

2.Определите словами понятие "ночного сотрудника" и "вылавливание"?

3.Таблицы tIn и tOut лишние. Можно объединить таблицу tInput саму с собой, чтобы получить нужные данные.
11 сен 14, 16:23    [16564813]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
По поводу отсеивания времени см. функцию convert. Но это, мне кажется, лишнее.
11 сен 14, 16:25    [16564822]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
k_mak
Member

Откуда:
Сообщений: 33
Помогите советом, пожалуйста.
Как из данных отсеять только минимальное и максимальное значение по каждому дню?
делаю таким запросом
select fio,event,cardID,time, MIN(time) as пришел, MAX(time) as ушел from tAllPersons
where fio like 'Комаров%'
group by fio,event,cardID,time


т.е на скрине мне нужно взять 1 и 20 записи и поместить их в соответствующие столбцы.

К сообщению приложен файл. Размер - 38Kb
23 сен 14, 09:38    [16608184]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
k_mak
Как из данных отсеять только минимальное и максимальное значение по каждому дню?

select fio,event, cardID, convert(date, time) as time, MIN(time) as пришел, MAX(time) as ушел 
from tAllPersons
where fio like 'Комаров%'
group by fio,event,cardID, convert(date, time)
23 сен 14, 10:15    [16608368]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
alexeyvg
k_mak
Как из данных отсеять только минимальное и максимальное значение по каждому дню?

select fio,event, cardID, convert(date, time) as time, MIN(time) as пришел, MAX(time) as ушел 
from tAllPersons
where fio like 'Комаров%'
group by fio,event,cardID, convert(date, time)
"отсеять" - это разве не "отбросить"?
Так что ответ неверный
23 сен 14, 10:28    [16608453]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
k_mak
Member

Откуда:
Сообщений: 33
alexeyvg , спасибо, это то, что нужно =)

iap, не, все отлично, это я туплю
23 сен 14, 13:19    [16609967]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
k_mak
alexeyvg , спасибо, это то, что нужно =)

iap, не, все отлично, это я туплю
Это я так пошутил
23 сен 14, 13:38    [16610127]     Ответить | Цитировать Сообщить модератору
 Re: Вычисление разницы значений времени у двух таблиц  [new]
k_mak
Member

Откуда:
Сообщений: 33
И снова здравствуйте..!=)
Вновь столкнулся с неразрешимым для меня вопросом((
Я не понимаю, что можно сделать в такой ситуации..

Немножко дополнил запрос alexeyvg
select fio, event, cardID, convert(date, time) as time, MIN(time) as пришел, MAX(time) as ушел, CONVERT(time,(max(time) - min(time))) as work_time
from tallpersons
group by fio, event, cardID, convert(date, time)

в результате, конечно же, расчет времени получился неверный..т.к перескочил день..

Приложил скрин.

18 и 19 записи – как раз такой момент – сотрудник пришел на работу 23.09 в 09.17, ушел 24.09 в 00.59, а затем вновь пришел 24.09 в 9.00..и по запросу MIN(time) и MAX(time) на 23.09 является единственная запись в 09.17..а MIN(time) на 24 число берет конечно же запись в 00.59..но она должна при расчетах использоваться относительно к дате 23.09 (в виде MAX(time))

Можно-ли несложными усилиями решить данный момент? Или тут уже более серьезный запрос с фильтрами и условиями нужен?

К сообщению приложен файл. Размер - 105Kb
24 сен 14, 17:29    [16617348]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить