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

Откуда:
Сообщений: 21
Всем привет. Такой вопрос, может мне тут помогут. В общем , надо подсчитать разницу во времени между интервалами входа и выхода сотрудников.
Таблица имеет такой вид. Ка подсчитать

10/28/2014 9:10:42 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card OUT
10/28/2014 9:10:01 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card IN
10/28/2014 9:05:42 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card OUT
10/28/2014 9:05:34 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card IN
10/28/2014 9:04:23 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card OUT
10/28/2014 9:03:44 AM 8 Person 192.168.1.201 192.168.1.201-1 Only Card IN
10/28/2014 8:57:16 AM 8 person 192.168.1.201 192.168.1.201 -1 Only Card OUT


Также, могут быть два выхода подряд, либо два входа. Как оформить это? Подскажите пжл
28 окт 14, 15:33    [16768142]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
PhoenixFire, Вы сперва оформите алгоритм, как это видите и как должно работать, а реализовать будет уже проще
28 окт 14, 15:44    [16768228]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
nizamovich
Member

Откуда: Москва
Сообщений: 116
PhoenixFire,

1. пронумеровать строки ROW_NUMBER() OVER(partition by person_name order by date) as RN
2. соединить таблицу с самим собой со смещением, условие джоина t1.person_name = t2.person_name и t1.RN = t2.RN
и вытащить разницу времени datediff(...)
28 окт 14, 15:49    [16768259]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
nizamovich
Member

Откуда: Москва
Сообщений: 116
nizamovich
PhoenixFire,

1. пронумеровать строки ROW_NUMBER() OVER(partition by person_name order by date) as RN
2. соединить таблицу с самим собой со смещением, условие джоина t1.person_name = t2.person_name и t1.RN = t2.RN
и вытащить разницу времени datediff(...)


немного очепятался

t1.RN +1 = t2.RN
28 окт 14, 15:51    [16768275]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
nizamovich
nizamovich
PhoenixFire,

1. пронумеровать строки ROW_NUMBER() OVER(partition by person_name order by date) as RN
2. соединить таблицу с самим собой со смещением, условие джоина t1.person_name = t2.person_name и t1.RN = t2.RN
и вытащить разницу времени datediff(...)


немного очепятался

t1.RN +1 = t2.RN


а что делать при
PhoenixFire
Также, могут быть два выхода подряд, либо два входа
28 окт 14, 15:57    [16768318]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

Откуда:
Сообщений: 21
Я как-то не очень в SQL Server, но сумел там кое что сделать из того что мне нужно, осталось эту фигню проработать.
Думаю, первый вход не должен браться в расчёт, а брать уже остальные. То есть брать следующую пару Выход-Вход и из времени входа вычесть время выхода. Но реализация...
Я понимаю что вот так просить не красиво, но надо...
28 окт 14, 16:03    [16768349]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

Откуда:
Сообщений: 21
может быть какую нибудь хранимую процедуру надо написать??
28 окт 14, 16:08    [16768384]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
Arm79
Member

Откуда: МО, Раменское
Сообщений: 3692
Особого смысла нет. Вы же сами написали, возможно и два/три/четыре выхода, аналогично со входами. Человек может пройти за кем то в открытую дверь, тогда он не приложит карту к датчику.

Есть еще вариант, что зашел он в один день, а вышел - в другой, то есть деление по датам тоже не вариант.

Либо напишите текстово тут, как именно вы видите алгоритм подсчета, либо берите последний выход минус первый вход (скорректируйте алгоритм чтобы учитывал интервалы, размазанные по дням)
28 окт 14, 16:15    [16768432]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
PhoenixFire, идите в поиск, были похожие задания. Искомое слово "периоды"
28 окт 14, 16:15    [16768434]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
aleks2
Guest
nizamovich
PhoenixFire,

1. пронумеровать строки ROW_NUMBER() OVER(partition by person_name order by date) as RN
2. соединить таблицу с самим собой со смещением, условие джоина t1.person_name = t2.person_name и t1.RN = t2.RN
и вытащить разницу времени datediff(...)

1. Это тупо и глупо.

2. Повторы - удалить. Какой оставлять - решать тредстартеру.

3. dT = сумма ( Tвых_i - Твход_i ) = сумма ( Tвых_i ) - сумма ( Твход_i )

4. И никакого джойна не надо.
28 окт 14, 16:16    [16768436]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

Откуда:
Сообщений: 21
Так дело в том что со временем они поймут что надо использовать карту доступа, так как из-за этого у ни будут вычитаться часы раб.времени. Брать надо первые пары выход-вход и все, если между ним и есть еще выходы либо входы, просто игнорировать
28 окт 14, 16:22    [16768471]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
PhoenixFire,

С моей точки зрения никакой ROW_NUMBER и JOIN тут не нужен.

Нужно грамотное применение оконных функций.

Вы на каком сервере это делаете, потому что тут LAG LEAD напрашивается , но это SQL 2012

Иначе что-то такое
MAX(CASE WHEN ...условие для предыдущей записи... THEN date END) OVER(partition by person_name order by date) as R
28 окт 14, 16:30    [16768523]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
Arm79
Member

Откуда: МО, Раменское
Сообщений: 3692
PhoenixFire
Так дело в том что со временем они поймут что надо использовать карту доступа, так как из-за этого у ни будут вычитаться часы раб.времени. Брать надо первые пары выход-вход и все, если между ним и есть еще выходы либо входы, просто игнорировать


Учет посуточный:
1) Ищем первое упоминание о сотруднике. Если это выход (или вообще нет никаких упоминаний), то за начало периода берем 00:00:00 суток. Если это вход, то он и есть начало периода
2) Ищем последнее упоминание о сотруднике. Если это вход (или вообще нет никаких упоминаний), то конец периода - 23:59:59:997. Если это выход, то его время и является концом периода.
3) Ищем в периоде все пары вышел-зашел, и суммируем время отсутствия
4) Вычитаем из длительности периоды просуммированное время отсутствия.

На глаз как то так
28 окт 14, 16:31    [16768527]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

Дело в том что мне на каждый день надо смотреть эти данные, то есть все интервалы выходы-входы ( время отсутствия на рабочем месте)
28 окт 14, 16:35    [16768558]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

SQL SERVER 2008 R2 но можно и на 2012, не проблема...
28 окт 14, 16:35    [16768571]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
Arm79
Member

Откуда: МО, Раменское
Сообщений: 3692
PhoenixFire
Arm79,

Дело в том что мне на каждый день надо смотреть эти данные, то есть все интервалы выходы-входы ( время отсутствия на рабочем месте)

а кто мешает то? я ж так и написал - посуточный учет. Могу переформулировать - учет по дням. Если присмотреться, в алгоритме есть работа с началом и концом дня
28 окт 14, 16:42    [16768624]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

да, извините, не заметил, просто забегался на работе :)
в принципе то что вы написали, это то что мне нужно... вот только как это реализовать???
28 окт 14, 16:45    [16768639]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
aleks2
Guest
Да уж, раздули на ровном месте. Оконные функции... умора.

declare @t table(dt datetime, pid int, inout bit, primary key clustered ( dt, pid ));
declare @x datetime = getdate();

insert @t (dt , pid, inout)
values ( '20141026 10:00', 1, 0 )
     , ( '20141026 10:10', 1, 1 )
     , ( '20141026 10:15', 1, 0 )     
     , ( '20141026 10:30', 1, 1 )          
     
select pid, sum( case inout when 0 then -1  when 1 then 1 end * datediff(minute, @x, dt) ) as WorkTime_minutes from @t t group by pid;


Удаление дублей, я надеюсь, тредстартеру напишут здешние знатоки.
28 окт 14, 16:46    [16768655]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

спасибо, начинаю вроде что-то понимать, но... работает ли такой запрос если повторяются?
И это можно как-то приспособить к моей таблице? или просто имена полей поменять просто?
28 окт 14, 16:56    [16768720]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
aleks2
Guest
PhoenixFire
aleks2,

спасибо, начинаю вроде что-то понимать, но... работает ли такой запрос если повторяются?
И это можно как-то приспособить к моей таблице? или просто имена полей поменять просто?

Блин, до чего непонятливый попался.

Повторы надо просто удалить ДО подсчета.
Как ты их удалишь - твое дело.
Я бы тупо удалил совсем из таблицы.
Шоб не париться.
28 окт 14, 16:58    [16768747]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

Откуда:
Сообщений: 21
Ну вот такой, ничего нельзя поделать. :) Просто в компаний >200 людей, нельзя ли чтобы эти данные автоматически брались из таблицы которая уже есть?
Я еще раз извиняюсь за глупые вопросы, просто реально нужно и реально не знаю и не совсем понимаю...
28 окт 14, 17:02    [16768771]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
declare @t table(dt datetime, pid int, inout bit, primary key clustered ( dt, pid ));
declare @x datetime = getdate();

insert @t (dt , pid, inout)
values ( '20141026 10:00', 1, 0 )
     , ( '20141026 10:10', 1, 1 )
     , ( '20141026 10:15', 1, 0 )     
     , ( '20141026 12:30', 1, 1 )  
insert @t (dt , pid, inout)
values ( '20141027 10:00', 1, 0 )
     , ( '20141027 10:10', 1, 1 )
     , ( '20141027 10:15', 1, 0 )     
     , ( '20141027 10:30', 1, 1 )  
insert @t (dt , pid, inout)
values ( '20141026 10:00', 2, 0 )
     , ( '20141026 10:10', 2, 1 )
     , ( '20141026 10:15', 2, 0 )     
     , ( '20141026 10:30', 2, 1 )  
insert @t (dt , pid, inout)
values ( '20141027 10:00', 2, 0 )
     , ( '20141027 10:10', 2, 1 )
     , ( '20141027 10:15', 2, 0 )     
     , ( '20141027 11:30', 2, 1 )  

SELECT 
	PID, 
	MINUTES_IN_OFFICE = DATEDIFF(minute, MIN(CASE WHEN inout = 0 THEN dt END), MAX(CASE WHEN inout = 1 THEN dt END)), 
	DT_ = CONVERT(CHAR(10), dt, 121) 
FROM @t t
GROUP BY PID, CONVERT(CHAR(10), dt, 121)
28 окт 14, 18:35    [16769115]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

Я в принципе понял как работает этот запрос, но мне нужно знать, можно как-то связать его с моей таблицей, чтобы не вводить все эти данные в ручную :(
28 окт 14, 18:39    [16769126]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
PhoenixFire
a_voronin,

Я в принципе понял как работает этот запрос, но мне нужно знать, можно как-то связать его с моей таблицей, чтобы не вводить все эти данные в ручную :(


Не понял. Делайте SELECT из вашей таблицы или что вы хотите ?
28 окт 14, 18:42    [16769137]     Ответить | Цитировать Сообщить модератору
 Re: Подсчитывание интервалов в СУБД SQL Server  [new]
PhoenixFire
Member

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

вот все то что де SELECT, оставлять так?? или менять на название полей из моей таблицы?
28 окт 14, 18:45    [16769146]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить