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

← →
Sens © (2004-04-13 08:34) [0]

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

← →
ЮЮ © (2004-04-13 08:45) [1]

>и при этом, чтобы он выполнялся быстро

Для этого каждая запись "выхода" должна ОЗНОЗНАЧНО соответствовать записи на "вход". Тогда, соединив через JOIN, получим пары вход-выход. Естественно, лучше организовать на уровне структуры данных

← →
Sergey13 © (2004-04-13 08:45) [2]

Вообще, не дурно бы знать как ты хранишь все это добро, но нечто вроде
select max(time_field)-min(time_field) FROM Table1
where....
даст тебе разницу.

← →
Anatoly Podgoretsky © (2004-04-13 08:52) [3]

ЮЮ © (13.04.04 08:45) [1]
В реальности для проходных такое точное соответствие не всегда бывает. А второй вариант первой (входной) и последней (выходной) вообще бессмысленный - вошел 2.01.2001, выше 13.04.2004. Какой то смысл появляется если применить формулу последней входной и последней выходной, предпоследней входной и предпоследней выходной и так далее.
Несложно организуется проходом по двум наборам данным - входа и выхода.

← →
sniknik © (2004-04-13 08:57) [4]

может и получится
select sum(Case номер направление when 0 then -1 else 1 end * время прохода) FROM Table1

(вариацию с приведением типа лутше сделать, это только идея) ну и подумать на тему если чел в ночную работает, сегодня пришол завтра ушол (через дырку в заборе ;о)) а данные по дню групируются...

← →
Sergey13 © (2004-04-13 09:04) [5]

2Anatoly Podgoretsky © (13.04.04 08:52) [3]
"Та заводская проходная, что в люди вывела меня", написанная мной на Клиппере, крутится уже 12 лет. Не далее как час назад "пробился" на входе. Там очень дофига "если бы да кабы", но именно поэтому я и написал "нечто вроде". У меня например сначала идет проверка на "парность" вход/выход. Далее эти записи (нарушители проходного режима) в расчете не учавствуют, точнее по ним расчитывается время нарушения (тоже кстати с большими допущениями из-за неопределенностей).

← →
Sens © (2004-04-13 09:15) [6]

==========================================================
1 2 3 4 5 6 7
1144 | 1 | 1 | 1 | 0 | 0 | 2003-10-22 07:15:00 - пришел на работу
1144 | 1 | 1 | 0 | 0 | 0 | 2003-10-22 12:02:45 - обед выход
1144 | 1 | 1 | 1 | 0 | 0 | 2003-10-22 12:56:17 - обед вход
1144 | 1 | 1 | 0 | 0 | 0 | 2003-10-22 17:02:51 - ушел с работы
==========================================================

1 colAccountNumber - ключ, номер человека
2 colCardholder
3 colPointNumber
4 colDirection - направление прохода
5 colZoneTarget - куда вышел
6 colStatusNumber - статус (разрешен ли доступ или отказано по какой-то причине)
7 colRegistrationTime - время регистрации

Проблема какраз состоит в том, чтобы выбрать 1-ю и последнюю запись. Понятно что можно вызвать запрос, со всеми тегистрациями, взять прервую запись и последнюю запись в из результата, это получится время прихода и ухода, а как выкинуть время обеда ???

← →
Sergey13 © (2004-04-13 09:31) [7]

ИМХО, "стандартными" запросами тут не пройдет. Надо писать программу - логика там больно неопределенная, вернее условий сильно много (например чел в отпуске и зашел на полчаса деньги получить). А в таблице с проходами можно выставлять флажок - запись уже обработана да/нет.

← →
Sens © (2004-04-13 09:35) [8]

Вот и я о том-же сделать можно, но представите сколько прога быдет все это вычислять для того, чтобы сделать табель выходов на работу за месяц для 1400 человек, если в этой таблице уже почти 900 000 записей...
Вот и написал посоветоваться, одна голова хорошо, а форум лучше :)))

Горе мне - горе...
:))

← →
Sergey13 © (2004-04-13 09:46) [9]

2Sens © (13.04.04 09:35) [8]
>если в этой таблице уже почти 900 000 записей...
Ну дык, чисти ее. Кому нужен прошлогодний снег? На крайний случай скидывай "месячные итоги" в архивную таблу и чисти оперативную.

← →
Nikolay M. © (2004-04-13 09:47) [10]

Имхо, не стоит мудрить, пытаясь достать эту сумму одним запросом. Лепил я в целях повышения образованности такие десятиэтажные запросы, где одна таблица джойнилась сама на себя раз 5 + куча CASE-ов. И не факт, что в итоге получится правильно.
Имхо, небольшая хранимая процедурка + курсор на SELECT, в котором данные отсортированы по дате входа/выхода + несколько IF-ов - как раз то, что нужно.

← →
Anatoly Podgoretsky © (2004-04-13 09:54) [11]

Ты учитывай и непарность
IN,OUT,IN,IN,IN,OUT,IN,OUT,OUT,IN
И переход через сутки, месяц, а у тебя еше и тип входв/выхода, может оказаться - обед выход, пришел на работу
Различные графики работы, логика может оказаться очень сложной, придется применять ИИ, а то у тебя очень такая красивая табличка :-)

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

Order by klient, date

← →
Nikolay M. © (2004-04-13 10:14) [12]

> логику делать на клиенте.

на фига??? По шее потом получить?

← →
Anatoly Podgoretsky © (2004-04-13 10:16) [13]

Не дело сервера заниматься логическими вычислениями.

← →
Nikolay M. © (2004-04-13 10:20) [14]

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

← →
Sens © (2004-04-13 10:28) [15]

2 Anatoly Podgoretsky
Да, логика будет сложная, но я пока планирую сделать не много... учет за день, а потом вывести в отчет дни за месяц, повыбрасывав из них выходные и праздничные дни (это и будет табель выходов на работу). У наших расчетчиков учитываются отработанные часы/мес.

Последую примеру и буду логику делать на клиенте.

Спасибо Всем за идеи. ;)

← →
Sergey13 © (2004-04-13 10:40) [16]

2Sens © (13.04.04 10:28) [15]
>но я пока планирую сделать не много
Так, скорее всего не получится. Или все или ничего. Прикинь, чела вызвали в военкомат, например. Это ГО (гособязанность)-оплата 100%. Больничные, отгулы (с документальным подтверждением), уходы по производственным делам и т.д. и т.п. Без таких вещей твой табель нужен только в туалете, если конечно бумага мягкая. 8-)
А кроме всего прочего (у нас железо самой проходной доисторическое), бывает что данных просто нет в принципе. Зимой замерзло, летом вспотело. 8-) А люди то ходят, и что самое удивительное - некоторые работают.

>У наших расчетчиков учитываются отработанные часы/мес.
Добавь сразу возможность ручной корректировки результатов, 8-) иначе... 8-(

← →
Anatoly Podgoretsky © (2004-04-13 10:44) [17]

Nikolay M. © (13.04.04 10:20) [14]
Какие многоэтажные запросы, просто bwtween d1 and d2, индексы будут учитываться, по его данным это примерно 6000 записей на месяц.

← →
Ega23 © (2004-04-13 10:53) [18]

Anatoly Podgoretsky © (13.04.04 10:44) [17]

Если не 5000 человек на предприятии

Кстати, не забывайте и такой факт:
допустим нужно получить табель сотрудников за вчера. А кто-то в ночную смену работал. Т.е. первое событие за этот день в системе у него - ВЫХОД.

← →
Johnmen © (2004-04-13 10:54) [19]

>Sens ©

Можно запросом :
SELECT colAccountNumber, SUM(datetimediff) FROM (SELECT T1.colAccountNumber, MIN(CAST(T2.colRegistrationTime-T1.colRegistrationTime AS FLOAT)) AS datetimediff FROM Table T1 JOIN Table T2 ON (T2.colAccountNumber=T1.colAccountNumber) AND (T1.colZoneTarget=1) AND (T2.colZoneTarget=0) AND (T1.colRegistrationTime<=T2.colRegistrationTime) GROUP BY T1.colAccountNumber, T1.colRegistrationTime ) AS T GROUP BY colAccountNumber

← →
sniknik © (2004-04-13 10:59) [20]

проверил (правда в access) идею в [4] по данным в Sens © (13.04.04 09:15) [6]
(просто для примера)
ничего, работает
SELECT CDate(SUM(iif(client_id =1,-1,1)*servicedate)) FROM Table2
результат
30.12.1899 8:54:19
ну 30.12.1899 это ноль, а время на работе -> 8:54:19, кстати переработка!! ;о)
(таблицу взял первую попавшуюся, т.е. названия полей мои)

← →
Delirium © (2004-04-13 11:04) [21]

Ещё вариант быстрого совмещения времен, поле A - вход/выход, поле B - время, на поле С во временной таблице рекомендую наложить кластерный индекс

select *,
Identity(int, 1, 1) as C
into #t
from t
order by B

select *
from #t t1
join #t t0 on t1.C=t0.C-1
where (t1.A=1) and (t0.A=0)

← →
Delirium © (2004-04-13 11:12) [22]

Кстати, потестировав, могу сказать, что вышеозначеный индекс на больших объёмах даёт выигрыш более чем в 60 раз.

create unique clustered index #ix on #t (C asc)

← →
Anatoly Podgoretsky © (2004-04-13 11:28) [23]

Ega23 © (13.04.04 10:53) [18]
Не забываю, день минус 1 и анализ на отсутствия выхода (с временем более последнего входа) в этот день. Там уже варианты относить в предудущему месяцу, к текущему или разбить по обеим месяцам пропорционально. Другой вариант, входа нет, считать за первое число и время 0.

← →
Sens © (2004-04-13 11:45) [24]

2 sniknik
Подправь пожалуйста, где-то я проглючил

SELECT CDate(SUM(iif(tblAllRegistrations.colAccountNumber =1144,-1,1)*colRegistrationTime)) FROM dbo.tblAllRegistrations
-------------------------------------------
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near "=".
-------------------------------------------

← →
Delirium © (2004-04-13 11:48) [25]

MSSQL не знает что такое iif

← →
sniknik © (2004-04-13 11:49) [26]

Sens © (13.04.04 11:45) [24]
это для аксеса, для mssql будет по другому (первый вариант что приводил), счас проверю точнее (может там вообще не пойдет (не верю ;о)).

для MSSQL

SELECT
CAST(SUM(CASE AcNum WHEN 1 THEN -CAST(RegTime AS FLOAT) ELSE CAST(RegTime AS FLOAT) END) AS DateTime)
FROM Table2

да 1 у меня это похоже 1144 у тебя, и дата будет
01.01.1900 8:54:19
вместо
30.12.1899 8:54:19
но это все одно ноль ;о))

Что-то у меня опять не как у людей 8-(
Или я тупой или лыжи не едут!

SELECT CAST(SUM(CASE dbo.tblAllRegistrations.colAccountNumber WHEN 1144 THEN -CAST(dbo.tblAllRegistrations.colRegistrationTime AS FLOAT) ELSE CAST(dbo.tblAllRegistrations.colRegistrationTime AS FLOAT) END) AS DateTime) FROM dbo.tblAllRegistrations Where dbo.tblAllRegistrations.colRegistrationTime between "01.03.2004" AND "02.03.2004"
============================================================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
=============================================================

А чем не устроил [19] ?

2 Johnmen © (13.04.04 12:35) [31]

Пробовал, но у меня что-то страшное получилось. Не могу понять, как можно таблицу саму с собой связать.

SELECT
colAccountNumber,
SUM(datetimediff)
FROM
(SELECT
dbo.tblAllRegistrations.colAccountNumber,
MIN(CAST(dbo.tblAllRegistrations.colRegistrationTime-dbo.tblAllRegistrations.colRegistrationTime AS FLOAT)) AS datetimediff
FROM Table dbo.tblAllRegistrations
JOIN Table dbo.tblAllRegistrations ON (dbo.tblAllRegistrations.colAccountNumber=dbo.tblAllRegistrations.colAccountNumber) AND
(dbo.tblAllRegistrations.colZoneTarget=1) AND (dbo.tblAllRegistrations.colZoneTarget=0) AND
(dbo.tblAllRegistrations.colRegistrationTime<=dbo.tblAllRegistrations.colRegistrationTime)
GROUP BY dbo.tblAllRegistrations.colAccountNumber, dbo.tblAllRegistrations.colRegistrationTime
) AS T
GROUP BY dbo.tblAllRegistrations.colAccountNumber

2 Johnmen © (13.04.04 12:35) [31]

как результат:
===================================
Server: Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword "Table".
===================================

> Arithmetic overflow error converting expression to data type datetime.
попробуй не к float а к real приводить. унего разрядность больше.

Table в примере - это твоя таблица...
:)

Johnmen © (13.04.04 12:48) [35]

Так правильнее... оказывается у меня по асфальту лыжи не едут...
:)))

SELECT colAccountNumber, SUM(datetimediff) FROM (SELECT T1.colAccountNumber, MIN(CAST(T2.colRegistrationTime-T1.colRegistrationTime AS FLOAT)) AS datetimediff FROM dbo.tblAllRegistrations T1 JOIN dbo.tblAllRegistrations T2 ON (T2.colAccountNumber=T1.colAccountNumber) AND (T1.colZoneTarget=1) AND (T2.colZoneTarget=0) AND (T1.colRegistrationTime<=T2.colRegistrationTime) GROUP BY T1.colAccountNumber, T1.colRegistrationTime ) AS T GROUP BY colAccountNumber

> Arithmetic overflow error converting expression to data type datetime.
кстати возможно не к тому преобразованию относится где float а к
between "01.03.2004" AND "02.03.2004"
этому
задавай параметрами, в крайнеи случае так
between "20040201" AND "20040302"

Помогите вычислить промежуток времени между двумя записями в базе Найти похожие ветки