jeudi 9 juin 2016

report on daily attendance, door events database

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