locked
Detect last record for a user in a list and check for condition RRS feed

  • Question

  • I have a table with entry and exit records from two separate biometric devices. Reader with ID 56444 is entry and 56450 is for exit. I want to build a screen that will list at any time of the day persons currently on site( for emergency head count). This should not include those who have left the premises.

    I have built a query using the query designer that sorts on time all users of the system for the particular day. However, the list also includes those who have already left. as shown below

    a

    As an example, user Addo Eric is still inside the site. I want to list only those still inside. My idea is to check the last record for the user and check if still inside. How do i code this? Is this the best approach in solving the problem? Thanks in advance for all your suggestions! i am a newbee to programming and have struggled for about a week now!

    Friday, February 8, 2013 5:51 PM

Answers

  • I think the easiest way is to have a OnPremisesTable

    fields: personID, isOnPremises

    then for this OnPremisesTable make a query "SearchOnSitePersonnel" that basically search for isOnPremesis = true

    Then make a screen using that Query.

    Friday, February 8, 2013 6:13 PM
  • I have an idea for you:

    I rarely have to use them, but this may be a good place for a SQL Trigger.  You could write a trigger that fires when a logout record is inserted, that would update any previous login records for that individual.  If you add a LogoutTime field to the record, you could just update that record with the current DateTime so that it would be easy to tell when they logged out.

    You could then have a very simple query for the screen that just needs "...WHERE EventType = 56444 AND LogoutTime IS NULL".

    By doing this, you would catch any instance where the person logged in twice and then logged out once, or logged in once an logged out twice, though I don't know if that's possible.

    The trigger would be something like this:

    CREATE TRIGGER [dbo].[trig_LoggedOut] ON [dbo].[UserEvents]
        FOR INSERT	-- only fires when a record is added to the table
        						
    AS
    	
        UPDATE  dbo.UserEvents
        SET     UserEvents.LogoutTime = GETDATE()	-- update LogoutTime with current DateTime
        FROM    dbo.UserEvents e
                JOIN INSERTED i ON e.UserName = i.UserName
                                   AND e.EventDate IS NULL   -- only update records where LogoutTime is null
                                   AND e.EventType = 56444   -- only update login records
       			
        WHERE   i.EventType = 56450  -- only update when logout record is added
               					
               			
    GO

    • Edited by marks100 Friday, February 8, 2013 10:26 PM
    • Marked as answer by Dino HeModerator Monday, February 18, 2013 7:18 AM
    Friday, February 8, 2013 10:24 PM

All replies

  • I think the easiest way is to have a OnPremisesTable

    fields: personID, isOnPremises

    then for this OnPremisesTable make a query "SearchOnSitePersonnel" that basically search for isOnPremesis = true

    Then make a screen using that Query.

    Friday, February 8, 2013 6:13 PM
  • I have an idea for you:

    I rarely have to use them, but this may be a good place for a SQL Trigger.  You could write a trigger that fires when a logout record is inserted, that would update any previous login records for that individual.  If you add a LogoutTime field to the record, you could just update that record with the current DateTime so that it would be easy to tell when they logged out.

    You could then have a very simple query for the screen that just needs "...WHERE EventType = 56444 AND LogoutTime IS NULL".

    By doing this, you would catch any instance where the person logged in twice and then logged out once, or logged in once an logged out twice, though I don't know if that's possible.

    The trigger would be something like this:

    CREATE TRIGGER [dbo].[trig_LoggedOut] ON [dbo].[UserEvents]
        FOR INSERT	-- only fires when a record is added to the table
        						
    AS
    	
        UPDATE  dbo.UserEvents
        SET     UserEvents.LogoutTime = GETDATE()	-- update LogoutTime with current DateTime
        FROM    dbo.UserEvents e
                JOIN INSERTED i ON e.UserName = i.UserName
                                   AND e.EventDate IS NULL   -- only update records where LogoutTime is null
                                   AND e.EventType = 56444   -- only update login records
       			
        WHERE   i.EventType = 56450  -- only update when logout record is added
               					
               			
    GO

    • Edited by marks100 Friday, February 8, 2013 10:26 PM
    • Marked as answer by Dino HeModerator Monday, February 18, 2013 7:18 AM
    Friday, February 8, 2013 10:24 PM