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

Откуда: Ростов-на-Дону
Сообщений: 97
Доброго времени суток!

Простая на первый взгляд задача. Как реализовать - не знаю. Нужно посчитать сколько времени человек провел на работе.
С помощью запроса:
SELECT 
  dbo.pList.Name,
  dbo.pLogData.TimeVal,
  dbo.pLogData.Mode
FROM
  dbo.pLogData
  INNER JOIN dbo.pList ON (dbo.pLogData.HozOrgan = dbo.pList.ID)
WHERE
  dbo.pLogData.Event = 32
  AND dbo.pLogData.TimeVal > '20121016'
  AND dbo.pLogData.TimeVal < '20121017'
ORDER BY
  dbo.pList.Name, dbo.pLogData.TimeVal


получил такие данные:
[IMG]http://www.imageup.ru/img205/thumb/date1079337.jpg[/IMG]

Уважаемые знатоки, внимание! Вопрос! Как посчитать кол-во часов, которое сотрудник находился на работе на определенную дату, если 1 - вход, 2 - выход (столбец MODE)
21 окт 12, 22:01    [13353417]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
Alexander Titkin
Member

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

Соединить dbo.pLogData(mode=1) c dbo.pLogData (mode=2) c минимально ближайшей датой к mode=1 и пройтись datediff() по двум датам. Также не забыть обыграть вход без выхода и выход без входа
21 окт 12, 23:33    [13353759]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
aleks2
Guest
Alexander Titkin
PunK_HS,

Соединить dbo.pLogData(mode=1) c dbo.pLogData (mode=2) c минимально ближайшей датой к mode=1 и пройтись datediff() по двум датам. Также не забыть обыграть вход без выхода и выход без входа


Чему нонича в ясельках учат? Зачем тут соединять?

1. Время пребывания на работе = СУММА ПО i( ВремяВыход(i)-ВремяВход(i) ) от первого входа до последнего
= СУММА ПО i( ВремяВыход(i) ) - СУММА ПО i ( ВремяВход(i) )
= СУММА ПО j ( если вход тогда -Время иначе Время конец ) для всех входов и выходов в интервале рабочего дня

2. Число входов и выходов должно быть одинаково.
22 окт 12, 06:50    [13354256]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
Alexander Titkin
Member

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

Дяде Аркадию - сторожу с правом ношения травматики и возвращающемуся каждое утро со смены, потом объясните про ясли и четное количество выходов-выходов
22 окт 12, 11:26    [13355226]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
PunK_HS
Member

Откуда: Ростов-на-Дону
Сообщений: 97
Alexander Titkin, могли бы Вы объяснить более подробно. Опыта программирования в SQL нет никакого. Может быть есть пример подобной реализации?
22 окт 12, 15:02    [13356998]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
Добрый Э - Эх
Guest
PunK_HS,

лучше приведи тестовый набор данных исходной таблицы в виде WITH t as (...).
Задача - старая, как мир. Решений много - как навоза на свиноферме.
Но так лень набирать данные в ручную, чтобы показывать эти решения на твоем конкретном примере...
22 окт 12, 15:33    [13357192]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
KRS544
Member

Откуда:
Сообщений: 497
как то так
declare @RepDate datetime = '20120101'

select sum(DATEDIFF(mi,timeval,timeval2))
from
(select timeval,
  (select top 1 timeval from LOG l2 where l2.name='Вася' and l2.mode=2 and l2.LID>l1.LID) as timeval2
 from LOG l1  
 where l1.name='Вася' and l1.mode=1 and timeval>@RepDate) a
22 окт 12, 16:20    [13357501]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
KRS544
Member

Откуда:
Сообщений: 497
сорри, там где select top - надо еще в конце order by обязательно добавить
22 окт 12, 16:22    [13357520]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
aleks2
Guest
Alexander Titkin
aleks2,

Дяде Аркадию - сторожу с правом ношения травматики и возвращающемуся каждое утро со смены, потом объясните про ясли и четное количество выходов-выходов


Может ты и сторожем работаешь... тут я могу тока посочувствовать.
Но при "соединении" тибе, единохренственно, придется обрабатывать ситуацию с травматикой. Увы и ах.
22 окт 12, 17:14    [13357913]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
KRS544
Member

Откуда:
Сообщений: 497
declare @RepDate datetime = '20120101',
        @Name varchar(50) = 'qq'

select sum(DATEDIFF(mi,timeval,isnull(timeval2,GetDate()))) as WorkSmenaTime
from
(select timeval,
  (select top 1 timeval from LOG l2 where l2.name=@Name and l2.mode=2 and l2.LID>l1.LID order by l2.LID asc) as timeval2
 from LOG l1  
 where l1.name=@Name and l1.mode=1 and timeval>@RepDate) a

Это время рабочей смены, т.к. смена может начинаться и ночью.
22 окт 12, 17:28    [13357979]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
Alexander Titkin
Member

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

Вы бы поделикатнее с форумчанами, свое Я поглубже и конструктивненько, не отходя от темы... Мне ваши выпадки ни к чему
22 окт 12, 21:19    [13359003]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
PunK_HS
Member

Откуда: Ростов-на-Дону
Сообщений: 97
KRS544,
DECLARE @RepDate1 DATETIME    = '20121016',
        @RepDate2 DATETIME    = '20121017',
        @Name     VARCHAR(50) = 'Попов'

SELECT sum(datediff(mi, pLogData.TimeVal, isnull(enddate, getdate()))) AS WorkSmenaTime
FROM
  (SELECT pLogData.TimeVal
        , (SELECT TOP 1 pLogData.TimeVal
           FROM
             pLogData
           INNER JOIN pList
           ON pLogData.HozOrgan = pList.id
           WHERE
             pList.Name = @Name
             AND pLogData.mode = 2
             AND pLogData.TimeVal BETWEEN @RepDate1 AND @RepDate2
           ORDER BY
             pList.name ASC) AS enddate
   FROM
     pLogData
   WHERE
     pList.Name = @Name
     AND pLogData.mode = 1
     AND pLogData.TimeVal BETWEEN @RepDate1 AND @RepDate2) a


Преобразовал до такого вида. Не знаю, как заставить работать(
22 окт 12, 22:50    [13359372]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL Express группировка по дате  [new]
tetxx
Member

Откуда:
Сообщений: 297
Ещё вариант, в одно чтение
declare @pLogData as table(empid integer,mode integer,TimeVal datetime)

insert into @pLogData values
(1,0,'01.01.2012 09:00'),
(1,0,'02.01.2012 09:00'),
(1,0,'02.01.2012 09:00'),
(1,1,'02.01.2012 20:00'),
(1,1,'02.01.2012 20:00'),
(1,0,'03.01.2012 09:00'),
(1,1,'03.01.2012 20:00')


select empid,SUM(datediff(mi,[0],[1])) s
from (
  select empid, TimeVal, mode, ROW_NUMBER()over(order by TimeVal)-mode r
  from @pLogData
  where empid = 1
)t
pivot(min(TimeVal)for mode in ([0],[1]))pvt
where pvt.[0] is not null and pvt.[1] is not null
group by empid
23 окт 12, 00:45    [13359736]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить