I have table [acc_monitor_log]. Which has raw events stored for employees.
I convert raw logs to basic columns what are needed.
SELECT convert(char(10), al.[time], 105) [exactdate]
, convert(char(5), al.[time], 108) [exacttime]
, u.[Name]
, u.[lastname]
, u.[Badgenumber]
,[type of entry] = case when al.[state] = 1 then 'Exit' else 'Entry' end
,al.[event_point_name]
FROM [access].[dbo].[acc_monitor_log] al, [access].[dbo].[USERINFO] u
where
[time] >= '01-06-2016'
and al.[card_no] = u.[CardNo]
and u.[Name] <> 'Vizitator'
and al.[event_point_name] like '%AC-%'
So results are something like this.
exactdate exacttime Name lastname Badgenumber type of entry event_point_name
01-06-16 7:57 user1 user1 9 Entry Floor.5
01-06-16 12:04 user1 user1 9 Exit Floor.5
01-06-16 13:08 user1 user1 9 Entry Floor.4
01-06-16 17:27 user1 user1 9 Exit Floor.4
02-06-16 7:43 user2 user2 10 Entry Floor.5
02-06-16 12:02 user2 user2 10 Exit Floor.5
08-06-16 13:01 user2 user2 10 Entry Floor.5
08-06-16 17:29 user2 user2 10 Exit Floor.5
I need to find out employees who went out during lunch 12:00 - 13:00 but did not return on the same day. What troubles me that I need to have it covering whole month. If I needed that within a day, I think I wouldn't be stuck with this.
How can I exclude rows from the same day? Ideally I would need list of employees who went out on which day and did not return on that day.
Aucun commentaire:
Enregistrer un commentaire