Employee Access used following Employee Separation

Transact-SQL
1

We have buildings located around the world. Entry to building is controlled by a badge access system which records cards used in a transaction journal. There are 14 million entries in that access journal. With the help of the group, I have identified 400 active building access cards that belong to employees who are have been separated according to our HR records. Now I have been asked to determine the number of times the card assigned to the employee was used following their separation.

Example Journal Entries
Date Time Action Name Card Building Direction
04-14-2016 15:13:31 Admitted Wray, Michael 312774 NY01 01 Turnstile G Bank (3) (33-41) In
04-14-2016 15:13:33 Admitted Leonard, John 88531 CO07 19/02 SOUTH ENTRY In
04-14-2016 15:13:34 Admitted OWANO, JAY 50964 IL01 29/06 S&P RATINGS SOUTHEAST ENTRY In
04-14-2016 15:13:38 Admitted Ragbeer, Toolsie 314049 NY01 01 Turnstile E Bank (3) (27) In

Example Overdue Badge Access Report
cc_Card# cc_Employ_ID cc_First cc_Last cc_Person_Type cc_Expiration Date cc_Record_Date HR_Termination_Date TERMINATION_REASON_CF
599 810812765 DIANA MARIA SERNA GIRALDO Employee 6/30/2057 19:00 3/1/2017 2/17/2017 VOLUNTARY
30315 710716506 Daniel Tanz Employee 6/30/2057 19:00 3/1/2017 12/31/2014 INVOLUNTARY

2

are you attempting to parse the data from the journal entries are is it already in a table?
you will need to group the query by employee id, date entry
then do a count by employee by date

3

Please provide:

  • table definitions in the form of create statements
  • valid sample data in the form of insert statements
  • expected output from the sample data you provide

Are cardno. reused when employee stops?

4

OK I realize I have been to vague. As inputs I two datasets,

1 - ONE .the journal of card activity with timestamps ( 12 million entries)
Date Time Action Name Card Building Direction
04-14-2016 15:13:31 Admitted Wray, Michael 312774 NY01 01 Turnstile G Bank (3) (33-41) In
04-14-2016 15:13:33 Admitted Leonard, John 88531 CO07 19/02 SOUTH ENTRY In
04-14-2016 15:13:34 Admitted OWANO, JAY 50964 IL01 29/06 S&P RATINGS SOUTHEAST ENTRY In
04-14-2016 15:13:38 Admitted Ragbeer, Toolsie 314049 NY01 01 Turnstile E Bank (3) (27) In

    • TWO
      cc_Card# cc_Employ_ID cc_First cc_Last HR_Termination_Date ( 800 Records)
      599 810812765 DIANA MARIA SERNA GIRALDO 2/17/2017
      30315 710716506 Daniel Tanz 12/31/2014
      31247 710796450 James Sackel 9/3/2013
      35282 710832267 Hai Xia Tina Ji 12/31/2016

I though to appending a column to the second dataset with the last login data taken from the first dataset. I used the following

SELECT DISTINCT
[cc_Card#]
,J.[Date] AS [Last Logon Date]
FROM [Corporate].[dbo].[Badge_Access]
LEFT JOIN [Badge Card Log] J
ON [Card#]=J.Card
WHere J.[Date] = (SELECT MAX([Date]) FROM [Badge Card Log])

However this on matches 38 rows, strange.

5

I have no way of seeing the field types (ex. is HR_Termination_Date a date field, datetime field or string), so it's almost impossible to come up with optimal query - hense my request for [quote="bitsmed, post:3, topic:9778"]
table definitions in the form of create statements
[/quote]

Also the sample data you provided (which is not in consumable format - insert statement), does not give me any way to check the joining of tables, and will then result in no output.

You didn't answer (or explain):

Assuming the "date" field in table "badge card log" is of "datetime" type, "hr_termination_date" in table "field in badge_access" is of "date" type and cardno is NOT reused, this might work:

select j.*
  from corporate.dbo.badge_access as a
       inner join [badge card log] as j
               on j.card=a.[card#]
              and j.[date]>=cast(a.hr_termination_date as datetime)
;

If cardno IS reused, you might try to join on name aswell:

select j.*
  from corporate.dbo.badge_access as a
       inner join [badge card log] as j
               on j.card=a.[card#]
              and j.[date]>=cast(a.hr_termination_date as datetime)
              and j.name=a.cc_last+', '+a.cc_first
;
6

Just a guess...

Select t1.cc_Card#
     , j.Date As [Last Logon Date]
  From Corporate.dbo.Badge_Access t1
 Outer Apply (Select Top 1 
                     * 
                From dbo.[Badge Card Log] t2
               Where t2.Card = t1.[Card#]
               Order By
                     t2.Date desc) As j

This will give you all of the rows from Corporate.dbo.Badge_Access - and only those that match from [Badge Card Log]. To get only those rows that match - change it to a Cross Apply.

7

Hope those are not real people's names

1 Like