Question

FahadRana212 on Sun, 21 Jan 2018 13:37:05


Hi, We have attendance system and  I am looking for some help to write a sql query to calculate time interval of different shifts employees when they in and out. I got success to calculate day shift employees but for night shift employees I need help to calculate their time interval. Any one can help with this ?

CREATE VIEW [dbo].[TimeAttendanceQuery]
AS
SELECT        TOP (100) PERCENT dbo.AxPerson.Name, dbo.AxPerson.IdNumber AS EmployeeID, dbo.TimeAttendance.Badge, dbo.AxPerson.Id, MIN(dbo.TimeAttendance.EventTime) AS EntryTime, 
                         MAX(dbo.TimeAttendance.EventTime) AS ExitTime, CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime), MAX(dbo.TimeAttendance.EventTime)) / 60 / 60 / 24 AS NVARCHAR(50)) 
                         + ':' + CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime), MAX(dbo.TimeAttendance.EventTime)) / 60 / 60 % 24 AS NVARCHAR(50)) + ':' + CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime), 
                         MAX(dbo.TimeAttendance.EventTime)) / 60 % 60 AS NVARCHAR(50)) AS TimeInterval, dbo.TimeAttendance.Event, dbo.AxPerson.Type AS ShitType, dbo.AxPerson.ShiftDesc, CONVERT(Varchar, 
                         dbo.TimeAttendance.EventTime, 101) AS EventDate
FROM            dbo.AxPerson INNER JOIN
                         dbo.TimeAttendance ON dbo.AxPerson.Name = dbo.TimeAttendance.Name
GROUP BY dbo.AxPerson.Name, dbo.AxPerson.IdNumber, dbo.TimeAttendance.Badge, CONVERT(Varchar, dbo.TimeAttendance.EventTime, 101), dbo.AxPerson.ShiftDesc, dbo.AxPerson.Id, dbo.TimeAttendance.Event, 
                         dbo.AxPerson.Type
ORDER BY dbo.AxPerson.Name, EventDate DESC

GO

The above help me to join two tables and calculate successful time interval for same day attendance. I want it to be modified to calculate night shift employees time interval as well which normally check out on next day.

Any suggestion or advice would be highly appreciated.


Sponsored



Replies

Uri Dimant on Sun, 21 Jan 2018 13:46:23


Can you post sample data + desired result?

FahadRana212 on Sun, 21 Jan 2018 14:31:59


Here is the information:

Time Attendanc table:

EventTime Badge IdNumber Location Event
22/12/2017 06:14:06 0-1089 10000086 Entry Valid access
22/12/2017 18:18:59 0-1048 10000060 Entry Valid access
23/12/2017 06:32:19 0-1048 10000060 Exit Valid access
22/12/2017 20:18:45 0-1089 10000086 Exit Valid access

Axperson Table:

Name IdNumber Id Location Type ShiftDesc
Rosita, Kimuel 0-1089 U780255 Entry Shift B Shift Employee
AlGhubari, Ibrahim 0-1048 U759537 Entry Shift B Shift Employee
Rosita, Kimuel 0-1048 U755207 Exit Shift B Shift Employee
AlGhubari, Ibrahim 0-1089 U781368 Exit Shift B Shift Employee

Desired Output:

Name Employee ID Badge ID SYSTEMID Check In Check Out Time Duration Access Shift Type Shift   Description Date
AlGhubari, Ibrahim U780255 0-1048 10000060 12/22/2017 18:18 12/23/2017 6:39 12:20:52 Valid access Shift B Shift Employee 12/22/2017
AlMohammed Saleh, Ahmed U759537 0-1028 10000062 12/22/2017 18:20 12/23/2017 6:33 12:13:17 Valid access Shift B Shift Employee 12/22/2017
AlMubarak, Mustafa U755207 0-1010 10000066 12/22/2017 18:19 12/23/2017 6:38 12:19:11 Valid access Shift B Shift Employee 12/22/2017
Al-Nutayf, Aqeel U781368 0-1057 10000061 12/22/2017 18:19 12/23/2017 6:31 12:11:52 Valid access Shift B Shift Employee 12/22/2017
AlAli, Mohammed U781370 0-1059 10000065 12/22/2017 6:20 12/22/2017 18:33 12:13:10 Valid access General Shift Day Staff 12/22/2017
Ansari, Mohammed U783782 0-1079 10000096 12/22/2017 7:39 12/22/2017 18:33 10:54:10 Valid access General Shift Day Staff 12/22/2017
Rosita, Kimuel UA18879 0-1089 10000086 12/22/2017 6:14 12/22/2017 20:18 14:04:39 Valid access General Shift Day Staff 12/22/2017
Al-Aseel, Mahmood U755188 0-1009 10000083 12/22/2017 6:19 12/22/2017 18:41 12:22:40 Valid access Shift D Shift Employee 12/22/2017
AlAsmari, Hussam U781390 0-1062 10000073 12/22/2017 6:32 12/22/2017 18:29 11:57:19 Valid access Shift D Shift Employee 12/22/2017
AlBuainain, Yousif U781392 0-1064 10000075 12/22/2017 6:21 12/22/2017 18:34 12:12:50 Valid access Shift D Shift Employee 12/22/2017
AlJaifi, Faisal U787601 0-1085 10000080 12/22/2017 6:22 12/22/2017 18:36 12:13:35 Valid access Shift D Shift Employee 12/22/2017
AlQahtani, Abdulaziz U781406 0-1071 10000079 12/22/2017 6:32 12/22/2017 18:30 11:58:12 Valid access Shift D Shift Employee 12/22/2017

FahadRana212 on Sun, 21 Jan 2018 14:53:41


can you help ?I showed the results

Visakh16 on Sun, 21 Jan 2018 15:37:28


can you help ?I showed the results

Your output doesnt match with the posted data

How did you get 12/23/2017 6:39 for  badgeID 0-1048

As per data shown it is 23/12/2017 06:32:19 for exit

FahadRana212 on Sun, 21 Jan 2018 15:42:49


I copy paste wrongly rom sheet. I will share the correct rows.

FahadRana212 on Sun, 21 Jan 2018 16:18:51


Here is the sample data :

Time Attendance:

EventTime Badge IdNumber Location Event Name
22/12/2017 06:14:06 0-1089 10000086 Entry Valid access Rosita, Kimuel
22/12/2017 18:18:59 0-1048 10000060 Entry Valid access AlGhubari, Ibrahim
12/23/17 6:39:51 0-1048 10000060 Exit Valid access Rosita, Kimuel
22/12/2017 20:18:45 0-1089 10000086 Exit Valid access AlGhubari, Ibrahim

AxPerson Table:

Name IdNumber Id Location Type ShiftDesc
Rosita, Kimuel 10000086 U780255 Entry Shift B Shift Employee
AlGhubari, Ibrahim 10000060 U759537 Entry Shift B Shift Employee
Rosita, Kimuel 10000060 U755207 Exit Shift B Shift Employee
AlGhubari, Ibrahim 10000086 U781368 Exit Shift B Shift Employee

Visakh16 on Sun, 21 Jan 2018 19:55:16


I think something like this

;With CTE
AS
(
SELECT *
,LEAD(EventTime,1) OVER (PARTITION BY Badge,IdNumber,Name ORDER BY EventTime) AS NextTime
,LEAD(EventTime,1) OVER (PARTITION BY Badge,IdNumber,Location ORDER BY EventTime) AS NextLocTime
,LEAD(Location,1) OVER (PARTITION BY Badge,IdNumber,Name ORDER BY EventTime) AS NextLocation
FROM TimeAttendanc
)

SELECT a.*,
t.EventTime AS CheckIn,CASE WHEN NextLocation = 'Exit' THEN NextTime ELSE NextLocTime END AS CheckOut,
FORMAT(DATEADD(ss,DATEDIFF(ss,t.EventTime,CASE WHEN NextLocation = 'Exit' THEN NextTime ELSE NextLocTime END),0),'HH:m:ss') AS TimeDuration,
t.Badge,
t.Event AS Access
FROM Axperson a
JOIN CTE t
ON t.IdNumber = a.IdNumber
AND t.Name = b.Name
WHERE t.Locaton = 'Entry'

This may again be far from perfect as sample data is very little and doesnt help much to understand various use cases. For example will there be corresponding Exit entry for employee at all times, what should be done in case of missing entry. Can a shift overlap multiple days (over 24 hrs ) etc

--CELKO-- on Sun, 21 Jan 2018 23:12:35


Why did you fail to post DDL? Read the stuff at the front of this forum about doing this sort of minimal netiquette. It would also help greatly if you knew that the only format allowed for temporal data in ANSI/ISO standard SQL is based on ISO 8601 (yyyy-mm-dd HH:mm:ss) , and not your local dialect. I guess we can you start guessing it keys relationship supporting tables and all the other things that you couldn't be bothered to tell us. Does your boss treat you like this? Do you work for free, too? 

Xi Jin on Mon, 22 Jan 2018 03:08:12


Hi FahadRana,

First I would suggest you to verify your sample data again. Since your query is grouping on dbo.AxPerson.Id. They are all different in your second sample data for each Employee.

Then the problems that make your query unworkable is because you are grouping on CONVERT(Varchar,dbo.TimeAttendance.EventTime, 101) AS EventDate. When the check out date is on next day, the CONVERT(Varchar,dbo.TimeAttendance.EventTime, 101) AS EventDate will return a second value for the Employee. And this make your query grouping failed. You can simply verify this with following query (A little tip, please use aliases for your tables. It will make the query easy to read):

create table TimeAttendance
(
EventTime datetime,
Badge varchar(10),
IdNumber bigint,
Location varchar(10),
Event varchar(20),
Name varchar(50)
)

insert into TimeAttendance values
('2017-12-22 06:14:06','0-1089',10000086,'Entry','Valid access','Rosita, Kimuel'),
('2017-12-22 18:18:59','0-1048',10000060,'Entry','Valid access','AlGhubari, Ibrahim'),
('2017-12-22 20:18:45','0-1089',10000086,'Exit','Valid access','Rosita, Kimuel'),
('2017-12-23 6:39:51','0-1048',10000060,'Exit','Valid access','AlGhubari, Ibrahim')

create table AxPerson
(
Name varchar(50),
IdNumber bigint,
Id varchar(10),
Location varchar(10),
Type varchar(10),
ShiftDesc varchar(20)
)

insert into AxPerson values
('Rosita, Kimuel',10000086,'U780255','Entry','Shift B','Shift Employee'),
('AlGhubari, Ibrahim',10000060,'U759537','Entry','Shift B','Shift Employee'),
('Rosita, Kimuel',10000086,'U755207','Exit','Shift B','Shift Employee'),
('AlGhubari, Ibrahim',10000060,'U781368','Exit','Shift B','Shift Employee')

select a.Name,a.IdNumber as EmployeeID,t.Badge,MIN(t.EventTime) as EntryTime,MAX(t.EventTime) as ExitTime,
t.Event,a.Type as ShiftType,a.ShiftDesc,CONVERT(Varchar, t.EventTime, 101) AS EventDate
from dbo.AxPerson a
INNER JOIN dbo.TimeAttendance t ON a.Name = t.Name
group by a.Name,a.IdNumber,t.Badge,t.Event,a.Type,a.ShiftDesc,CONVERT(Varchar, t.EventTime, 101) 
order by a.Name


As you can see in above query, the Employee AlGhubari, Ibrahim returns two rows. And for each row the EntryTime and ExitTime is same. That's why you cannot calculate the Time Duration.

To Modify your query, you can do the CONVERT(Varchar,dbo.TimeAttendance.EventTime, 101) later. Check this:

select *,CONVERT(Varchar, EntryTime, 101) AS EventDate
from (
	select a.Name,a.IdNumber as EmployeeID,t.Badge,MIN(t.EventTime) as EntryTime,MAX(t.EventTime) as ExitTime,
	FORMAT(DATEADD(SS,DATEDIFF(SS,MIN(t.EventTime),MAX(t.EventTime)),0),'HH:mm:ss') as TimeDuration,
	t.Event,a.Type as ShiftType,a.ShiftDesc
	from dbo.AxPerson a
	INNER JOIN dbo.TimeAttendance t ON a.Name = t.Name
	group by a.Name,a.IdNumber,t.Badge,t.Event,a.Type,a.ShiftDesc
) temp

Thanks,
Xi Jin.

FahadRana212 on Mon, 22 Jan 2018 07:42:30


Hi Xi Jin,

I will try this modified query and come back with the results. just wondering what is Temp at end of query?

Xi Jin on Mon, 22 Jan 2018 07:47:25


Hi FahadRana,

Since I'm doing a select from sub-query. I have to give this sub-query a name. So the temp is just a table alias for the sub-query.

Thanks,
Xi Jin.

FahadRana212 on Mon, 22 Jan 2018 10:52:23


Hi,

Can you show me results that query is generating at your side?

--CELKO-- on Mon, 22 Jan 2018 16:24:13


Why did you fail to post DDL? Please read the forum rules and follow basic netiquette. From what you did post, you do not understand what temporal intervals are.you can download a copy of the old Rick Snodgrass book on temporal queries in SQL for free as a PDF from the University of Arizona. Here is the basic skeleton for timecards:

CREATE TABLE Timecards
(emp_id CHAR(10) NOT NULL
   REFERENCES Personnel (emp_id)
   ON DELETE CASCADE
   ON UPDATE CASCADE,
 entry_timestamp DATETIME2(0) NOT NULL,
 exit_timestamp DATETIME2(0),
 CHECK (entry_timestamp < exit_timestamp),
 event_name VARCHR(25) NOT NULL,
..); 

we don’t have an explicit interval data type in SQL, so we have to do it with two columns. The start of a half open interval and the end of a half open interval (the ISO model) have to be in the same row because they apply is an attribute to the same entity. If the end of the interval is null. It means that the event has not completed. There are some other tricks with constraints to make sure there’s no gaps in the timeline, but that’s a bit advanced for this discussion. 

The calculation for the duration of an event is part of display and probably should not be in the database level of your tiered architecture. There is no such thing in RDBMS as a generic “name”; it has to be the name of something in particular. Likewise, there is no such thing as a generic “id”, etc. Get a good book on data modeling and learn how you name data elements in RDBMS(I do not recommend actually reading ISO 11179, since is written in “standard speak” in is about as much fun as trying to figure out tax laws). Why did you put an ORDER BY on a VIEW??  Think about it. 

What you’ve done is try to write a query to kludge a really bad schema into what it should have been in the first place. As the Turkish proverb says , “no matter how far you have gone down the wrong road, turnaround” and redo what you’ve got

Xi Jin on Tue, 23 Jan 2018 01:33:02


Hi,

The result is:

Thanks,
Xi Jin.

Xi Jin on Tue, 30 Jan 2018 01:53:29


Hi FahadRana,

Did you resolve your issue? If so, please kindly mark the corresponding reply. It will help others with the relevant issue find the answer more easily.

If not, please share us the situation you got.

Thanks,
Xi Jin.