Help

• gtrman57

  SSC Journeyman

  Points: 99

  More actions

  November 22, 2016 at 11:53 am

  #401106

  I have this sql query and it returns the results I am looking for. It gives me all the people who have entered a specific door within the last 30 days. I want to find anyone who has not used these doors in 30 days. Any help appreciated.

  DECLARE

  @StartDate DATEtime

  ,@EndDate DATEtime

  set @StartDate = DATEADD(day,-30, getdate())

  set @EndDate = GETDATE()

  SELECT PrimaryObjectName as name

  ,MessageType as admitted

  ,MessageUTC as access_time

  ,SecondaryObjectName as door

  FROM Journal.dbo.JournalLog

  WHERE SecondaryObjectName like 'DataCenter%'

  AND

  MessageUTC between @StartDate and @EndDate

  AND

  PrimaryObjectName in

  (SELECT distinct Personnel.Name

  FROM access.Personnel

  INNER JOIN

  access.PersonnelClearancePair ON Personnel.ObjectID = PersonnelClearancePair.PersonnelID

  INNER JOIN

  access.Clearance ON PersonnelClearancePair.ClearanceID = Clearance.ObjectID

  INNER JOIN

  access.ClearanceItem ON Clearance.ObjectID = ClearanceItem.ClearanceID

  INNER JOIN

  access.ScheduleItem ON ClearanceItem.ScheduleID = ScheduleItem.ObjectID

  LEFT OUTER JOIN

  access.Door ON ClearanceItem.DoorID = Door.ObjectID

  INNER JOIN

  access.Credential ON Credential.PersonnelId = Personnel.ObjectID

  WHERE (Door.Name LIKE 'DataCenter%')

  AND (Credential.Expired IS NULL)

  AND (Credential.Disabled = 0)

  AND (Credential.Stolen = 0)

  AND (Credential.Lost = 0)

  UNION

  SELECT distinct Personnel.Name

  FROM access.Personnel

  INNER JOIN

  access.PersonnelClearancePair ON Personnel.ObjectID = PersonnelClearancePair.PersonnelID

  INNER JOIN

  access.Clearance ON PersonnelClearancePair.ClearanceID = Clearance.ObjectID

  INNER JOIN

  access.ClearanceItem ON Clearance.ObjectID = ClearanceItem.ClearanceID

  INNER JOIN

  access.ScheduleItem ON ClearanceItem.ScheduleID = ScheduleItem.ObjectID

  INNER JOIN

  access.Group_ ON ClearanceItem.DoorGroupID = Group_.ObjectID

  LEFT OUTER JOIN

  access.Door

  INNER JOIN

  access.GroupMember ON Door.ObjectID = GroupMember.TargetObjectID ON Group_.ObjectID = GroupMember.GroupID

  INNER JOIN

  access.Credential ON Credential.PersonnelId = Personnel.ObjectID

  WHERE (Door.Name LIKE 'DataCenter%')

  AND (Credential.Expired IS NULL)

  AND (Credential.Disabled = 0)

  AND (Credential.Stolen = 0)

  AND (Credential.Lost = 0)

  AND (Group_.GroupType LIKE N'%Door%')

  OR (Group_.GroupType LIKE N'%Door%')

  AND (Group_.Name LIKE N'All Doors Group')

  AND (Credential.ExpirationDateTime > GETDATE())

  )

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 22, 2016 at 11:58 am

  #1914094

  you need a master list of all employees to compare against;

  then you can use an EXCEPT operator and your existing code

  I would stick your results in a temp table, and then you can use something like this:

  SELECT EmployeeName FROM AllEmployees

  EXCEPT

  SELECT PrimaryObjectName FROM #PreviousResults

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 22, 2016 at 12:11 pm

  #1914099

  You just need to invert the logic.

  DECLARE

  @StartDate DATE

  ,@EndDate DATE;

  set @StartDate = DATEADD( day,-30, getdate());

  set @EndDate = GETDATE();

  SELECT *

  FROM(

  SELECT p.Name, d.Name AS Door

  FROM access.Personnel p

  INNER JOIN access.PersonnelClearancePair pcp ON p.ObjectID = pcp.PersonnelID

  INNER JOIN access.Clearance c ON pcp.ClearanceID = c.ObjectID

  INNER JOIN access.ClearanceItem ci ON c.ObjectID = ci.ClearanceID

  INNER JOIN access.ScheduleItem si ON ci.ScheduleID = si.ObjectID

  INNER JOIN access.Door d ON ci.DoorID = d.ObjectID

  INNER JOIN access.Credential cr ON cr.PersonnelId = p.ObjectID

  WHERE d.Name LIKE 'DataCenter%'

  AND cr.Expired IS NULL

  AND cr.Disabled = 0

  AND cr.Stolen = 0

  AND cr.Lost = 0

  UNION

  SELECT p.Name, d.Name AS Door

  FROM access.Personnel p

  INNER JOIN access.PersonnelClearancePair pcp ON p.ObjectID = pcp.PersonnelID

  INNER JOIN access.Clearance c ON pcp.ClearanceID = c.ObjectID

  INNER JOIN access.ClearanceItem ci ON c.ObjectID = ci.ClearanceID

  INNER JOIN access.ScheduleItem si ON ci.ScheduleID = si.ObjectID

  INNER JOIN access.Group_ g ON ci.DoorGroupID = g.ObjectID

  INNER JOIN access.GroupMember gm ON g.ObjectID = gm.GroupID

  INNER JOIN access.Door d ON d.ObjectID = gm.TargetObjectID

  INNER JOIN access.Credential cr ON cr.PersonnelId = p.ObjectID

  WHERE d.Name LIKE 'DataCenter%'

  AND cr.Expired IS NULL

  AND cr.Disabled = 0

  AND cr.Stolen = 0

  AND cr.Lost = 0

  AND g.GroupType LIKE N'%Door%'

  OR g.GroupType LIKE N'%Door%'

  AND g.Name LIKE N'All Doors Group'

  AND cr.ExpirationDateTime > GETDATE()

  ) p

  WHERE NOT EXISTS (

  SELECT PrimaryObjectName as name

  ,MessageType as admitted

  ,MessageUTC as access_time

  ,SecondaryObjectName as door

  FROM Journal.dbo.JournalLog jl

  WHERE SecondaryObjectName like 'DataCenter%'

  AND MessageUTC between @StartDate and @EndDate

  AND jl.PrimaryObjectName = p.Name

  AND jl.SecondaryObjectName = p.Door);

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2