Post reply

Query to show who is still checked in.

  • September 3, 2021 at 2:35 pm

    #3926682

    Only the time?  Do you not have the date also?

    What happens at the end of the day, does the table wipe itself and start again?

    How do you differentiate a swipe from yesterday compared to today?

     

    But below will be one way to tackle the problem

    CREATE TABLE #CardSwipes (Initials VARCHAR(5), RFID INT, SwipeTime TIME, Flag TINYINT)

    INSERT INTO #CardSwipes VALUES
    ('AG',1234,'08:00',1),
    ('AG',1234,'09:00',2),
    ('AG',1234,'10:00',1),
    ('JS',5678,'20:00',1)

    ;WITH CardSwipeCTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY RFID ORDER BY SwipeTime DESC) AS RN,
    Initials, RFID, SwipeTime, Flag
    FROM #CardSwipes)
    SELECT * FROM CardSwipeCTE WHERE RN = 1 AND Flag = 1

  • September 3, 2021 at 2:50 pm

    #3926685

    Hi  Ant,

    With your code you advised and I have altered it this seems to working as i require:

    ;WITH CardSwipeCTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY RFID ORDER BY Time DESC) AS RN,
    data.Initial, Data.RFID, Data.Time, Data.Type
    FROM Data)
    SELECT * FROM CardSwipeCTE WHERE RN = 1 AND Type = 1 order by GETDATE() asc

    I will report back on further testing thank you.

     

     

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply