Member 13210334 Ответов: 2

Хранимая процедура для расчета общего количества рабочих часов с несколькими входами/выходами.

Ищете хранимую процедуру для расчета общего рабочего времени сотрудников с несколькими входами и выходами

Структура таблицы
Id-первичный let
EmpCode - код сотрудника
Статус-вход/выход
ActionDatetime - DateTime
Образец записи
1  emp100  IN     2017-05-18 10:00
2  emp100  OUT 2017-05-18  15:00
3   emp100  IN    2017-05-18  17:00
4  emp100  OUT  2017-05-18  20:00
5  emp102   IN   2017-05-18  06:00
6  emp102   OUT 2017-05-18 10:00
7 emp103  IN      2017-05-18 07:00
8  emp103  IN     2017-05- 18 10:00

Я должен пробовать данные, как описано выше.

Я хочу запустить хранимую процедуру с датой.

Это должно дать такой результат.

1. список кодов сотрудников без повторения кода сотрудников ( код сотрудников не может повторяться в строках)
2. Все и для всех сотрудников, как.

1. Emp100 IN 2017-05-18 10: 00 OUT 2017-05-18 15: 00 IN 2017-05-18 17: 00 OUT 2017-05-18 20: 00 (всего часов )


Если какой-либо вход или выход пропущен для бывших сотрудников, нечетные числа входов и выходов. Также продолжается та же последовательность того же статуса in In также начало дня с out without in it должно отображаться в последних Столбцах в виде замечаний.


Эта работа была опубликована с мобильного устройства, поэтому, пожалуйста, простите за любые опечатки или недостающие детали.

Что я уже пробовал:

USE [test]
GO
/****** Object: StoredProcedure [dbo].[GetWorkingHours] Script Date: 19-05-2017 12:07:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetWorkingHours]
@Day date
AS
BEGIN
SET NOCOUNT ON;

with EmpIn as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%IN%'
),
EmpOut as (
select row_number() over (order by [Date]) RowNum, [Date] as 'Date', EmpId as 'EmpId'
from EmpLog (nolock)
where cast([Date] as date) = @Day and [Status] LIKE '%OUT%'
)
select 
e1.EmpId as 'EmpId', 
e1.Date as 'In',
e2.Date as 'Out',
datediff(MINUTE, e1.[Date], e2.[Date]) as 'TotalHours'
from EmpIn e1
full outer join EmpOut e2 on e1.RowNum = e2.RowNum
end

CHill60

Игнорируя тот факт, что это должна быть хранимая процедура, какие запросы вы написали, чтобы попытаться получить эту информацию?
Если у вас буквально "нет идеи..." о том, как действовать дальше, тогда вы не поймете ничего из того, что мы вам дадим. Кстати мы не занимаемся написанием кода

Member 13210334

Используйте [тест]
ГО
/ * * * * * * Объект: StoredProcedure [dbo].[GetWorkingHours] Дата Написания Сценария: 19-05-2017 12: 07:55 ******/
УСТАНОВИТЕ ANSI_NULLS ON
ГО
УСТАНОВИТЕ QUOTED_IDENTIFIER НА
ГО
Изменить процедуру [dbo].[GetWorkingHours]
@День дата
АС
НАЧАТЬ
УСТАНОВИТЕ NOCOUNT ON;

с EmpIn как (
выберите row_number () over (order by [Date]) Параметр rownum, [дата], так как "дата", empid в качестве 'empid в'
от EmpLog (nolock)
где cast ([Date] as date) = @Day и [Status] LIKE ' %IN%'
),
Эмпаут как (
выберите row_number () over (order by [Date]) Параметр rownum, [дата], так как "дата", empid в качестве 'empid в'
от EmpLog (nolock)
где cast ([Date] as date) = @Day и [Status] LIKE '%OUT%'
)
выбирать
Е1.Empid в качестве 'empid в',
e1. дата как "в",
e2. дата как "выход",
datediff(минута, e1. [дата], e2. [дата]) как "TotalHours"
от EmpIn e1
полное внешнее соединение EmpOut e2 на e1.RowNum = e2. RowNum
конец

Christian Luketa Kanyinda

Привет,
Пожалуйста, постарайтесь лучше указать структуру таблицы.
Что вы подразумеваете под этой записью: emp100 в 2017-05-18 10: 00
В нем есть колонны ?
emp100 & 2017-05-18 10: 00 тоже

2 Ответов

Рейтинг:
2

OriginalGriff

Мы не делаем домашнее задание: оно задано не просто так. Она существует для того, чтобы вы думали о том, что вам сказали, и пытались понять это. Он также существует для того, чтобы ваш наставник мог определить области, в которых вы слабы, и сосредоточить больше внимания на корректирующих действиях.

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

CHill60

Я должен сказать, что если это домашнее задание, то это злое домашнее задание. Теперь я обеспокоен тем, что переоценил свое решение (что я и сделал в качестве упражнения)

Рейтинг:
2

CHill60

Я попробовал это сделать. Как я уже сказал в своем комментарии, я надеюсь, что это не домашнее задание, так как мне было довольно трудно получить всю нужную вам информацию.

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

SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
UNION ALL
SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut  WHERE CAST(ActionDatetime AS Date) = @Day
Это дало мне такие результаты:
EmpCode Status
emp100	IN
emp102	IN
emp103	IN
emp100	OUT
emp102	OUT
emp103	OUT
Если я затем запрошу эти результаты, то, присоединившись к таблице слева, я получу по крайней мере одну запись для каждого сотрудника для Status = IN и по крайней мере одну запись для каждого сотрудника для Status = OUT. Я также воспользуюсь этой возможностью, чтобы создать вход и выход столбец / i> И ранжируйте каждую запись по сотруднику по дате 
DECLARE @Day DATE = '2017-05-18'

if OBJECT_ID('tempdb..#shifts') is not null drop table #shifts
;with Expected as
(
	SELECT DISTINCT EmpCode, 'IN' AS [Status] FROM InOut WHERE CAST(ActionDatetime AS Date) = @Day
	UNION ALL
	SELECT DISTINCT EmpCode, 'OUT' AS [Status] FROM InOut  WHERE CAST(ActionDatetime AS Date) = @Day
)
select Id, AE.EmpCode, AE.[Status],ActionDatetime,
CASE WHEN AE.[Status] = 'IN' THEN ActionDatetime ELSE CAST(NULL AS datetime) END AS InTime,
CASE WHEN AE.[Status] = 'IN' THEN CAST(NULL AS datetime) ELSE ActionDatetime END AS OutTime
, RANK() OVER (PARTITION BY AE.EmpCode, AE.[Status] ORDER BY AE.EmpCode, ActionDatetime) rn
into #shifts
FROM Expected AE 
LEFT OUTER JOIN InOut actual ON AE.EmpCode = actual.EmpCode AND AE.[Status]=actual.[Status]
WHERE Id IS NOT NULL AND CAST(ActionDatetime AS Date) = @Day
дает мне результаты
Id EmpCode Status ActionDateTime   InTime           OutTime             rn
1  emp100  IN	  2017-05-18 10:00 2017-05-18 10:00 NULL	        1
2  emp100  OUT	  2017-05-18 15:00 NULL	            2017-05-18 15:00	1
3  emp100  IN	  2017-05-18 17:00 2017-05-18 17:00 NULL	        2
4  emp100  OUT	  2017-05-18 20:00 NULL	            2017-05-18 20:00	2
5  emp102  IN	  2017-05-18 06:00 2017-05-18 06:00 NULL	        1
6  emp102  OUT	  2017-05-18 10:00 NULL	            2017-05-18 10:00	1
7  emp103  IN	  2017-05-18 07:00 2017-05-18 07:00 NULL	        1
8  emp103  IN	  2017-05-18 10:00 2017-05-18 10:00 NULL	        2

Мы можем привести эти результаты в порядок с помощью GROUP BY
if OBJECT_ID('tempdb..#shifts2') is not null drop table #shifts2
SELECT EmpCode, rn, MIN(id) as Id,
MAX(InTime) as InTime, MAX(OutTime) AS OutTime
, CASE WHEN MAX(InTime) IS NULL or MAX(OutTime) IS NULL THEN 'Error' ELSE '' END AS Notes
INTO #shifts2
FROM #shifts
GROUP BY EmpCode, rn
Что дает мне гораздо более опрятные результаты:
empCode rn      Id      TimeIn            TimeOut           Notes
emp100	1	1	2017-05-18 10:00  2017-05-18 15:00	
emp100	2	3	2017-05-18 17:00  2017-05-18 20:00	
emp102	1	5	2017-05-18 06:00  2017-05-18 10:00	
emp103	1	7	2017-05-18 07:00  NULL	            Error
emp103	2	8	2017-05-18 10:00  NULL	            Error
Примечание. Вы можете игнорировать сообщение " предупреждение: нулевое значение исключается агрегатом или другой операцией набора."

На этом этапе вы можете выбрать поворот столбцов TimeIn и TimeOut но вместо этого я решил использовать функцию LEAD
;WITH res AS
(
	SELECT rn,EmpCode, InTime AS InTime1, OutTime as OutTime1,
	LEAD(InTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS InTime2,
	LEAD(OutTime) OVER (PARTITION BY EmpCode ORDER BY Id) AS OutTime2, 
	Notes
	FROM #shifts2
)
SELECT EmpCode,InTime1,OutTime1,InTime2,OutTime2, 
	isnull(DATEDIFF(hh, InTime1,OutTime1),0) + isnull(DATEDIFF(HH, InTime2, OutTime2),0) AS TotalHours
	, Notes
FROM res
WHERE rn = 1
Результаты:
emp100 2017-05-18 10:00 2017-05-18 15:00 2017-05-18 17:00 2017-05-18 20:00 8	
emp102 2017-05-18 06:00 2017-05-18 10:00 NULL	          NULL	           4	
emp103 2017-05-18 07:00 NULL	         2017-05-18 10:00 NULL	           0 Error