September 3, 2021 at 2:18 pm
Hi,
I am trying to write a query to see who is still checked-in. In our warehouse I have one table (data) with a Type field 1 = In and 2 = Out and Initial and RFID and Time
There could be multiple entries from one user per day and I am struggling to show who is still in the building.
Ideally the table would look like the below
Initial RFID TIME TYPE
JS 1234 13:40 1
DC 2345 12:30 1
Could someone point me in the right direction on how to achieve this.
Thanks James
September 3, 2021 at 2:35 pm
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
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply