Category: sql server transactsql
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.
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:
|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|
|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|
|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 :
|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|
|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
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
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
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.
FahadRana212 on Mon, 22 Jan 2018 10:52:23
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,
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
The result is:
Xi Jin on Tue, 30 Jan 2018 01:53:29
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.