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

Откуда: Novosibirsk
Сообщений: 68
Доброго времени суток.

Есть таблица входов-выходов работников:

CREATE TABLE [passage] (
[id] [int] IDENTITY (1, 1) NOT NULL,
[employee_id] [int] NOT NULL,-- ID сотрудника
[time] [datetime] NOT NULL,-- время входа или выхода
[flag] [int] NOT NULL -- 1 - вход, 2 - выход
) ON [PRIMARY]
Нужно составить хранимую процедуру (запрос), которая возвращала следующие результаты по сотрудникам (если возможно, то одним запросом):
1. Время первого входа
2. Время последнего выхода
3. Количество входов-выходов
4. Часов в здании
5. Часов вне здания
Количество входов работника в течении дня равно количеству выходов (четное).
29 ноя 05, 07:03    [2118635]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
anpv
Member

Откуда: Novosibirsk
Сообщений: 68
Забыл добавить за каждый день месяца.
Т.е. входные параметры процедуры: первый и последний день месяца.
А выходные: ID сотрудника, день месяца, первый вход, последний выход, количество входов-выходов, часов в здании, часов вне здания.
29 ноя 05, 07:11    [2118644]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
aleks2
Guest
select 	Day
	, employee_id
	, MIN([time]) FirstEnter
	, MAX([time]) LastLeave
	, COUNT(*) EntranceCount
	, SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)/60  OutTime
	, DATEDIFF(minute,MIN([time]),MAX([time])-SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END))/60 WorkTIme
from 
(SELECT cast(cast( [time] as int) as datetime) Day FROM [passage] WHERE @MonthBegin<=[time] AND [time]<cast(@MonthEnd+1 as datetime GROUP BY cast(cast( [time] as int) as datetime)) X
LEFT OUTER JOIN
[passage] T
ON
X.Day<=T.[time] AND T.[time]<cast(X.Day+1 as datetime)
GROUP BY Day, employee_id
29 ноя 05, 08:10    [2118691]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
John P.
Member

Откуда:
Сообщений: 14
declare
	@start datetime,
	@end datetime

select
	@start = '2005-11-01',
	@end = '2005-12-01'

select
	q1.employee_id, -- рабочий
	q1.param_day, -- день
	q1.input_time, -- дата первого входа
	q2.output_time, -- дата последнего выхода
	q1.turn_count + q2.turn_count, -- количество входов и выходов
	convert(decimal(10,1), (q2.in_time - q1.out_time) * 24), -- часов в здании в этот день
	convert(decimal(10,1), (24 - (q2.in_time - q1.out_time) * 24)) -- часов вне здания в этот день
from
	(
		select
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120) as param_day,
			min([time]) as input_time,
			count(*) as turn_count,
			sum(convert(float, [time]) - convert(float, convert(datetime, convert(char(10), time, 120), 120))) as out_time 
		from 
			[passage]
		where
			[time] between @start and @end
			and flag = 1
		group by	
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120)
	)Q1
	join
	(
		select
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120) as param_day,
			max([time]) as output_time,
			count(*) as turn_count,
			sum(convert(float, [time]) - convert(float, convert(datetime, convert(char(10), time, 120), 120))) as in_time 
		from 
			[passage]
		where
			[time] between @start and @end
			and flag = 2
		group by	
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120)
	)Q2 on q1.employee_id = q2.employee_id and q1.param_day = q2.param_day
29 ноя 05, 08:16    [2118697]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
aleks2
Guest
Прошу пардону за некоторые дефекты предыдущего решения... но проверить было не на чем.


select 	Day
	, employee_id
	, MIN([time]) FirstEnter
	, MAX([time]) LastLeave
	, COUNT(*)/2 EntranceCount
	, SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)  WorkTime_Minutes
	, (DATEDIFF(minute,MIN([time]),MAX([time]))-SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)) RestTime_Minutes
from 
(SELECT cast(cast( [time] as int) as datetime) Day FROM [passage] WHERE @MonthBegin<=[time] AND [time]<cast(@MonthEnd+1 as datetime) GROUP BY cast(cast([time] as int) as datetime)) X
LEFT OUTER JOIN
[passage] T
ON
X.Day<=T.[time] AND T.[time]<cast(X.Day+1 as datetime)
GROUP BY Day, employee_id
29 ноя 05, 10:16    [2119044]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
anpv
Member

Откуда: Novosibirsk
Сообщений: 68
Большое спасибо всем!
29 ноя 05, 10:19    [2119062]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Запрос для табеля рабочего времени  [new]
r12
Member

Откуда:
Сообщений: 63
Очень заинтересовал данный вопрос и я попытался его сделать (проверить), что бы потом переделать под себя
заполнил таблицу таблицу
1 12 2011-08-25 08:12:20.000 1
2 12 2011-08-25 08:21:21.000 2
4 12 2011-08-31 09:20:00.000 1
5 12 2011-08-31 09:21:01.000 2
6 15 2011-08-31 09:25:00.000 2

ввел ранее здесь написанный запрос
declare
	@start datetime,
	@end datetime

select
	@start = '2011-08-01',
	@end = '2011-09-01'

select
	q1.employee_id, -- рабочий
	q1.param_day, -- день
	q1.input_time, -- дата первого входа
	q2.output_time, -- дата последнего выхода
	q1.turn_count + q2.turn_count, -- количество входов и выходов
	convert(decimal(10,1), (q2.in_time - q1.out_time) * 24), -- часов в здании в этот день
	convert(decimal(10,1), (24 - (q2.in_time - q1.out_time) * 24)) -- часов вне здания в этот день
from
	(
		select
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120) as param_day,
			min([time]) as input_time,
			count(*) as turn_count,
			sum(convert(float, [time]) - convert(float, convert(datetime, convert(char(10), time, 120), 120))) as out_time 
		from 
			[passage]
		where
			[time] between @start and @end
			and flag = 1
		group by	
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120)
	)Q1
	join
	(
		select
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120) as param_day,
			max([time]) as output_time,
			count(*) as turn_count,
			sum(convert(float, [time]) - convert(float, convert(datetime, convert(char(10), time, 120), 120))) as in_time 
		from 
			[passage]
		where
			[time] between @start and @end
			and flag = 2
		group by	
			employee_id,
			convert(datetime, convert(char(10), time, 120), 120)
	)Q2 on q1.employee_id = q2.employee_id and q1.param_day = q2.param_day
при выводе результата у меня все пусто.......

если написать
@start = '2011-08-20',
@end = '2011-08-29'
то пишет ошибку:
Сообщение 242, уровень 16, состояние 3, строка 5
Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.
(строк обработано: 0)
я так понимаю интервалом должен быть целый месяц

также попробовал тоже ранее написанный запрос

select 	Day
	, employee_id
	, MIN([time]) FirstEnter
	, MAX([time]) LastLeave
	, COUNT(*)/2 EntranceCount
	, SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)  WorkTime_Minutes
	, (DATEDIFF(minute,MIN([time]),MAX([time]))-SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)) RestTime_Minutes
from 
(SELECT cast(cast( [time] as int) as datetime) Day FROM [passage] WHERE @MonthBegin<=[time] AND [time]<cast(@MonthEnd+1 as datetime) GROUP BY cast(cast([time] as int) as datetime)) X
LEFT OUTER JOIN
[passage] T
ON
X.Day<=T.[time] AND T.[time]<cast(X.Day+1 as datetime)
GROUP BY Day, employee_id

пишет:
Сообщение 137, уровень 15, состояние 2, строка 9
Необходимо объявить скалярную переменную "@MonthBegin".

я так понимаю надо где-то (как-то) ее объявить.... но я не знаю как :(

Подскажите что я делаю не так........

P.S. в sql не разбираюсь
31 авг 11, 10:27    [11204463]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
aleks2
Guest
r12
Подскажите что я делаю не так........

P.S. в sql не разбираюсь


Вот и разберись, чтоль?
31 авг 11, 10:39    [11204505]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
r12
Member

Откуда:
Сообщений: 63
дописал вот так:
use master
go	

declare
	@MonthBegin datetime,
	@MonthEnd datetime
select
	@MonthBegin = '2011-08-01',
	@MonthEnd = '2011-09-01'
select 	Day
    , employee_id
	, MIN([time]) FirstEnter
	, MAX([time]) LastLeave
	, COUNT(*)/2 EntranceCount
	, SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)  WorkTime_Minutes
	, (DATEDIFF(minute,MIN([time]),MAX([time]))-SUM(CASE Flag WHEN 1 THEN -DATEDIFF(minute,0,[time]) ELSE +DATEDIFF(minute,0,[time]) END)) RestTime_Minutes
from 
(SELECT cast(cast( [time] as int) as datetime) Day FROM [passage] 
WHERE @MonthBegin<=[time] AND [time]<cast(@MonthEnd+1 as datetime) GROUP BY cast(cast([time] as int) as datetime)) X
LEFT OUTER JOIN
[passage] T
ON
X.Day<=T.[time] AND T.[time]<cast(X.Day+1 as datetime)
GROUP BY Day, employee_id

теперь все переменные объявлены..... но мне кажется запрос не берет данные из указанной таблицы....
выводит пустой результат...
таблица находится в базе master.......
31 авг 11, 11:16    [11204687]     Ответить | Цитировать Сообщить модератору
 Re: Запрос для табеля рабочего времени  [new]
aleks2
Guest
Данные представь в скрипте... а то я уже и сам плохо понимаю чо там к чему - 6 лет прошло, аднако.
31 авг 11, 16:33    [11207467]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить