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

Откуда:
Сообщений: 1368
Есть данные:
id Sotr dt type
12 Вася 2015-09-04 20:38:48.000 0
13 Вася 2015-09-05 20:38:48.000 0
14 Петя 2015-09-05 08:38:48.000 1
15 Петя 2015-09-03 08:38:48.000 1
16 Петя 2015-09-03 10:38:48.000 1
17 Петя 2015-09-03 09:38:48.000 0
18 Петя 2015-09-03 20:38:48.000 0
20 Петя 2015-09-05 20:38:48.000 0
1003 Петя 2015-09-06 08:38:48.000 1
1006 Петя 2015-09-07 08:38:48.000 1
1010 Петя 2015-09-08 08:38:48.000 0


Нужно получить что-то вроде этого :
Sotr dt_enter dt_exit comment
Вася 2015-09-04 01:38:48.000 NULL нет выхода
Вася 2015-09-04 16:38:48.000 2015-09-04 20:38:48.000 NULL
Вася 2015-09-05 16:38:48.000 2015-09-05 20:38:48.000 NULL
Петя 2015-09-03 08:38:48.000 2015-09-03 09:38:48.000 NULL
Петя 2015-09-03 10:38:48.000 2015-09-03 20:38:48.000 NULL
Петя 2015-09-05 08:38:48.000 2015-09-05 20:38:48.000 NULL
Петя 2015-09-06 08:38:48.000 NULL нет выхода
Петя 2015-09-07 08:38:48.000 2015-09-08 08:38:48.000 NULL


ТОлько вот последняя строка должна быть на другой строке, а вместо него должен быть Null.
, т.е должны в строке быть дата входа и выхода одного дня.
Делаю запросом:
;with  cte 
as(
SELECT [id]
      ,[Sotr]
      ,[dt]
      ,[type]
	  , convert(date,dt,112) as dt1, row_number() over ( partition by sotr order by sotr,dt) as rw
  FROM [dbtest].[dbo].[Events] 
  
)

select c1.Sotr ,  
  (case c1.type  when 1 then c1.dt else null end) as dt_enter, c1.type ,
  (case c2.type  when 0 then c2.dt else null end) as dt_exit, c2.type as type_exit 
into #t
from cte  as c1
left join cte as c2 on c1.Sotr =c2.Sotr and c1.rw+1=c2.rw
--Select * from #t
select t1.Sotr , t1. dt_enter ,dt_exit 
 ---,DATEADD(mi,t1.dt_enter,t1.dt_exit ) as wrkt
 ,(case  when t1.dt_enter is null then 'нет входа'
         when t1.dt_exit  is null then 'нет выхода' end) as comment
from #t as t1 where t1.dt_enter is not null or t1.dt_exit is not null
order by t1.Sotr , t1.dt_enter 
drop table #t 

как добиться нужного?
если сделать через full
full join cte as c2 on c1.Sotr =c2.Sotr and c1.rw+1=c2.rw  and c1.dt1 =c2.dt1 

то лишние строки будут
10 сен 15, 00:32    [18130703]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
_human
Member

Откуда:
Сообщений: 560
смотрю_тут,
по-моему у вас не совпадают данные с желаемым результатом. возможно, так будет проще
; with src as (
	select * from 
	(values 
	(12, N'Вася', '2015-09-04 20:38:48.000', 0),
	(13, N'Вася', '2015-09-05 20:38:48.000', 0),
	(14, N'Петя', '2015-09-05 08:38:48.000', 1),
	(15, N'Петя', '2015-09-03 08:38:48.000', 1),
	(16, N'Петя', '2015-09-03 10:38:48.000', 1),
	(17, N'Петя', '2015-09-03 09:38:48.000', 0),
	(18, N'Петя', '2015-09-03 20:38:48.000', 0),
	(20, N'Петя', '2015-09-05 20:38:48.000', 0),
	(1003, N'Петя', '2015-09-06 08:38:48.000', 1),
	(1006, N'Петя', '2015-09-07 08:38:48.000', 1),
	(1010, N'Петя', '2015-09-08 08:38:48.000', 0)
	) q(id, Sotr, dt, type)
)

select p.*
	, case when p.[1] is null then 'no enter' else 
		case when p.[0] is null then 'no exit' else 'ok' 
		end 
	  end
from
(
	select s.Sotr, convert(date, s.dt) as date, s.dt, s.type
		, (row_number() over(partition by s.sotr, convert(date, s.dt) order by s.dt)+1)/2 as x
	from src s
) q
pivot(max(q.dt) for q.type in ([1], [0])) as p
10 сен 15, 02:38    [18130757]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
смотрю_тут
Member

Откуда:
Сообщений: 1368
да вроде то
только можно описать алгоритм,
не понял что это определяет
(row_number() over(partition by s.sotr, convert(date, s.dt) order by s.dt)+1)/2 as x
10 сен 15, 11:12    [18131634]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
_human
Member

Откуда:
Сообщений: 560
смотрю_тут,

в случае если сотрудник входил-выходил за день больше одного раза, нумерует входы-выходы
10 сен 15, 14:51    [18133131]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
смотрю_тут
Member

Откуда:
Сообщений: 1368
не подходит запрос:
29 Петя 2015-09-07 11:53:45.000 2015-09-07 00:22:26.000 ok
29 Петя 2015-09-07 12:16:27.000 2015-09-07 12:03:57.000 ok
29 Петя 2015-09-07 13:47:53.000 2015-09-07 13:34:23.000 ok
29 Петя 2015-09-07 17:29:48.000 2015-09-07 17:10:44.000 ok


при таких данных выход вчерашний попадает почему после входа уже нового дня и дальше сбиваются строки
10 сен 15, 20:05    [18134727]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
_human
Member

Откуда:
Сообщений: 560
смотрю_тут,

; with src as (
	select * from 
	(values 
	(12, N'Вася', '2015-09-04 20:38:48.000', 0),
	(13, N'Вася', '2015-09-05 20:38:48.000', 0),
	(14, N'Петя', '2015-09-05 08:38:48.000', 1),
	(15, N'Петя', '2015-09-03 08:38:48.000', 1),
	(16, N'Петя', '2015-09-03 10:38:48.000', 1),
	(17, N'Петя', '2015-09-03 09:38:48.000', 0),
	(18, N'Петя', '2015-09-03 20:38:48.000', 0),
	(20, N'Петя', '2015-09-05 20:38:48.000', 0),
	(1003, N'Петя', '2015-09-06 08:38:48.000', 1),

	(1005, N'Петя', '2015-09-07 00:22:26.000', 0),
	(1004, N'Петя', '2015-09-07 11:53:45.000', 1),
	(1006, N'Петя', '2015-09-07 12:16:27.000', 1),
	(1007, N'Петя', '2015-09-07 12:03:57.000', 0),
	(1008, N'Петя', '2015-09-07 13:47:53.000', 1),
	(1009, N'Петя', '2015-09-07 13:34:23.000', 0),
	(1011, N'Петя', '2015-09-07 17:29:48.000', 1),
	(1012, N'Петя', '2015-09-07 17:10:44.000', 0),


	(1010, N'Петя', '2015-09-08 08:38:48.000', 0)
	) q(id, Sotr, dt, type)
)

select p.*
	, case when p.[1] is null then 'no enter' else 
		case when p.[0] is null then 'no exit' else 'ok' 
		end 
	  end
from
(
	select s.Sotr, convert(date, s.dt) as date, s.dt, s.type
		, case when FIRST_VALUE(s.type) over(partition by s.sotr, convert(date, s.dt) order by s.dt) = 1
			then (row_number() over(partition by s.sotr, convert(date, s.dt) order by s.dt)+1)/2 
			else row_number() over(partition by s.sotr, convert(date, s.dt) order by s.dt)/2 end as x
	from src s
) q
pivot(max(q.dt) for q.type in ([1], [0])) as p

+ пошерстите форум, такой вопрос часто подымается
10 сен 15, 21:27    [18135018]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
смотрю_тут
Member

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

все равно не правильно считает
пример эти данные
4961 24 2015-09-03 13:20:32.000 I
4988 24 2015-09-03 00:26:01.000 I
5043 24 2015-09-03 17:28:47.000 O
5067 24 2015-09-03 18:00:55.000 I
5069 24 2015-09-03 18:21:20.000 I
5086 24 2015-09-03 21:31:59.000 I

он выводит как

24 2015-09-03 13:20:32.000 NULL 0 no exit
24 2015-09-03 18:00:55.000 2015-09-03 17:28:47.000 -1928 ok
24 2015-09-03 18:21:20.000 2015-09-03 18:03:34.000 -1066 ok
24 2015-09-03 21:31:59.000 2015-09-03 21:15:53.000 -966 ok
24 NULL 2015-09-04 10:22:06.000 0 no enter

ну и далее идет неправильно.
12 сен 15, 18:51    [18142551]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
aleks2
Guest
declare @t table (id int, Sotr nvarchar(128), dt datetime, type bit, 
                                     primary key clustered(Sotr, dt)  -- ЭТО ОЧЕНЬ ВАЖНЫЙ ИНДЕКС
                                   );

insert @t
	values 
	(12, N'Вася', '2015-09-04 20:38:48.000', 0),
	(13, N'Вася', '2015-09-05 20:38:48.000', 0),
	(14, N'Петя', '2015-09-05 08:38:48.000', 1),
	(15, N'Петя', '2015-09-03 08:38:48.000', 1),
	(16, N'Петя', '2015-09-03 10:38:48.000', 1),
	(17, N'Петя', '2015-09-03 09:38:48.000', 0),
	(18, N'Петя', '2015-09-03 20:38:48.000', 0),
	(20, N'Петя', '2015-09-05 20:38:48.000', 0),
	(1003, N'Петя', '2015-09-06 08:38:48.000', 1),

	(1005, N'Петя', '2015-09-07 00:22:26.000', 0),
	(1004, N'Петя', '2015-09-07 11:53:45.000', 1),
	(1006, N'Петя', '2015-09-07 12:16:27.000', 1),
	(1007, N'Петя', '2015-09-07 12:03:57.000', 0),
	(1008, N'Петя', '2015-09-07 13:47:53.000', 1),
	(1009, N'Петя', '2015-09-07 13:34:23.000', 0),
	(1011, N'Петя', '2015-09-07 17:29:48.000', 1),
	(1012, N'Петя', '2015-09-07 17:10:44.000', 0),
	(1010, N'Петя', '2015-09-08 08:38:48.000', 0),

--все равно не правильно считает
--пример эти данные 
	
(4961, N'Федя', '2015-09-03 13:20:32.000', 1), 
(4988, N'Федя', '2015-09-03 00:26:01.000', 1),  
(5043, N'Федя', '2015-09-03 17:28:47.000', 0), 
(5067, N'Федя', '2015-09-03 18:00:55.000', 1),  
(5069, N'Федя', '2015-09-03 18:21:20.000', 1),  
(5086, N'Федя', '2015-09-03 21:31:59.000', 1)  
	
;

select t.id, t.Sotr, t.type, t.dt  
       , case t.type 
          when CAST( 0 as bit ) then t.dt 
         end as dtIn
       , case t.type
          when CAST( 0 as bit ) then case n.type when CAST( 1 as bit ) then  n.dt  end
          when CAST( 1 as bit ) then t.dt
        end as dtOut
       , case when t.type = CAST( 0 as bit ) and n.type = CAST( 1 as bit ) then  n.id  end as idOut
  from @t t 
  outer apply (select top(1) * from @t where Sotr = t.Sotr  and dt > t.dt order by dt asc) n 

  -- ваще то, это излишество
  outer apply (select top(1) * from @t where Sotr = t.Sotr  and dt < t.dt order by dt desc) p

  where t.type = CAST( 0 as bit ) 

  -- ваще то, это излишество: выводит выходы без входа
    or ( t.type = CAST( 1 as bit ) and isnull(p.type, 1) <> CAST( 0 as bit ) )
13 сен 15, 10:20    [18143629]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
_human
Member

Откуда:
Сообщений: 560
смотрю_тут,

+
; with src as (
	select * from 
	(values 
	(12, N'Вася', '2015-09-04 20:38:48.000', 0),
	(13, N'Вася', '2015-09-05 20:38:48.000', 0),
	(14, N'Петя', '2015-09-05 08:38:48.000', 1),
	(15, N'Петя', '2015-09-03 08:38:48.000', 1),
	(16, N'Петя', '2015-09-03 10:38:48.000', 1),
	(17, N'Петя', '2015-09-03 09:38:48.000', 0),
	(18, N'Петя', '2015-09-03 20:38:48.000', 0),
	(20, N'Петя', '2015-09-05 20:38:48.000', 0),
	(1003, N'Петя', '2015-09-06 08:38:48.000', 1),

	(1005, N'Петя', '2015-09-07 00:22:26.000', 0),
	(1004, N'Петя', '2015-09-07 11:53:45.000', 1),
	(1006, N'Петя', '2015-09-07 12:16:27.000', 1),
	(1007, N'Петя', '2015-09-07 12:03:57.000', 0),
	(1008, N'Петя', '2015-09-07 13:47:53.000', 1),
	(1009, N'Петя', '2015-09-07 13:34:23.000', 0),
	(1011, N'Петя', '2015-09-07 17:29:48.000', 1),
	(1012, N'Петя', '2015-09-07 17:10:44.000', 0),
	
	(1010, N'Петя', '2015-09-08 08:38:48.000', 0),

	(4988, N'Кашпировский', '2015-09-03 00:26:01.000', 1),
	(4961, N'Кашпировский', '2015-09-03 13:20:32.000', 1),
	(5043, N'Кашпировский', '2015-09-03 17:28:47.000', 0),
	(5067, N'Кашпировский', '2015-09-03 18:00:55.000', 1),
	(5069, N'Кашпировский', '2015-09-03 18:21:20.000', 1),
	(5086, N'Кашпировский', '2015-09-03 21:31:59.000', 1),
	(5087, N'Кашпировский', '2015-09-03 22:32:59.000', 0),
	(5088, N'Кашпировский', '2015-09-03 23:33:59.000', 0),
	(5089, N'Кашпировский', '2015-09-03 23:37:59.000', 0)
	) q(id, Sotr, dt, type)
)
, s as 
(
			select s.Sotr, convert(date, s.dt) as date, s.dt, s.type
				, case when s.type > lead(s.Type) over(partition by s.Sotr, convert(date, s.dt) order by s.dt) OR
							s.type < lag(s.Type) over(partition by s.Sotr, convert(date, s.dt) order by s.dt)
						then 1 else 0 end as ok  -- находим пары вход-выход
			from src s
)

select *
from
(
	select p.Sotr, p.date, p.[1], p.[0], 'ok' as c
	from
	(

		select s.Sotr, convert(date, s.dt) as date, s.dt, s.type 
			,  (row_number() over(partition by s.sotr, convert(date, s.dt) order by s.dt)+1)/2  as x
		from s
		where s.ok = 1

	) q
	pivot(max(q.dt) for q.type in ([1], [0])) as p

	union all

	select s.Sotr, s.date
		, case when s.type = 1 then s.dt end as [1] 
		, case when s.type = 0 then s.dt end as [0]
		, case when s.type = 1 then 'no exit' else 'no enter' end
	from s
	where s.ok = 0

) r 
order by r.Sotr, isnull(r.[1], r.[0])


aleks2
primary key clustered(Sotr, dt) -- ЭТО ОЧЕНЬ ВАЖНЫЙ ИНДЕКС

разъясните почему, пожалуйста
14 сен 15, 01:15    [18145047]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
aleks2
Guest
_human
aleks2
primary key clustered(Sotr, dt) -- ЭТО ОЧЕНЬ ВАЖНЫЙ ИНДЕКС

разъясните почему, пожалуйста


А подумать?

ЗЫ. Пивот - не нужен.
14 сен 15, 12:23    [18146095]     Ответить | Цитировать Сообщить модератору
 Re: Запрос учета рабочего времение  [new]
_human
Member

Откуда:
Сообщений: 560
aleks2,
ну он ничего не аггрегирует - да, просто для разворота данных. можно через case сделать.
меня больше теоретический аспект интересует
14 сен 15, 16:06    [18147413]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить