March 27, 2012 at 8:06 am
I am going to type try to explain what is going on. Any help or advise will be greatly appreciated.
We have used RFID to capture attendee's at a conference. I was handed the data to try to make since of it. We need to determine in and out based on the following assumptions.
1. If there is less than 5 minutes between a RFID scan it is invalid
2. If there is not an even number of scan's for the record the last one will be considered invalid (they could have walked past the zone and not actually gone anywhere)
since this is a on off scenario there could be multiple on and multiple off scan's for a given ID. In the example text I sent you will see that from the ID 200804, the other leadretrievalid will have 2 that are valid and one that is not valid.
We need to be able to produce a chart (it could be in excel as well of how many people were on the floor and off the floor in a 1 hr increment. If a person a was on the floor at 9:00 am and left the floor at 9:45 he would just be counted twice once for being on the floor at 9:00 and once for leaving the floor at 9:45.
Or does anyone know how we can dynamically take all of the data in the record (well maybe not all) but like in the example if we can take the readerID,timestamp, and source and merge those into a single row, those out so they are on one row that would be alot easier to work with then the current format, then I can build a couple of assumptions together for the data and it will have to work this way.
Any help would be greatly appreciated. Any and all suggestions are welcomed.
[Code="sql"]
--If the testtable exist drop table
IF object_ID('tempdb..#testtable','U') IS NOT NULL
DROP TABLE #testtable
-- Create Test table
CREATE TABLE #testtable
(ID INT PRIMARY KEY CLUSTERED,
TIMESTAMP DATETIMEOFFSET,
leadretrievalid INT,
ZONE NVARCHAR(25),
reader NVARCHAR(25),
SOURCE NVARCHAR(25))
--insert data into table
INSERT INTO #testtable
( ID ,
TIMESTAMP ,
leadretrievalid ,
ZONE ,
reader ,
SOURCE
)
select '79696','2012-02-29 12:38:36.0000000 -08:00','200796','entrance1','RFID12','db' Union All
select '96446','2012-02-29 13:01:54.0000000 -08:00','200796','entrance2','RFID13','db' Union All
select '97009','2012-02-29 13:28:46.0000000 -08:00','200796','entrance2','RFID13','db' Union All
select '98926','2012-02-29 15:19:56.0000000 -08:00','200804','entrance2','RFID13','db' Union All
select '100287','2012-02-29 17:01:06.0000000 -08:00','200804','entrance2','RFID13','db' Union All
select '118446','2012-02-29 16:43:29.0000000 -08:00','200804','entrance3','RFID5','db' Union All
select '130008','2012-02-29 16:41:56.0000000 -08:00','200804','entrance4','RFID8','db'
[/code]
March 27, 2012 at 9:00 am
Can you show us the expected output out of the sample data you provided?
March 27, 2012 at 9:05 am
What I'm looking for is a matrix type of effect that will how like this
***Assumption
-----------------|-------------|------------|------------|
Time____________BadgeType1__BadgeType2__BadgeType3
8:00 a.m ________20___________15____________7
9:00 a.m.________5____________23____________12
etc
I need to have a count per hour breakdown, If they need more I can adjust whatever I need to to add the time intervals.
Hopefully this all makes since.
this will be broken down in Entry and exits. I'm assuming if there are scans in multiples of 2 then the second one will be a Out and the first one an in. If there is an odd number then i'll have to disregard the last one .
does this help
March 27, 2012 at 9:35 am
Is BadgeType = Reader in your sample data?
I dont seem to understand what is in your sample data and whats in your expected result. Also, can you be more clear on what the requirements are? I mean, can you do a walkthrough of your sample data , applying your rules and then deriving your ouput?
March 27, 2012 at 9:58 am
Sorry about that, the badge type will come from a different table in a seperate database, I just need to make since of the rfid data, and what is the in the sample data is exactly what I have to work with. That is in a seperate DB with only one table that will be linked back to a different table for the badge type.
Does that make since?
March 27, 2012 at 10:01 am
What I need to be able to determine is how many people where on the floor at a given time frame. As far as On the floor and Off the floor. I'm making the assumption that say for instance person 1 has a scan at 9:00, then another one at 12:30 then one at 3:00 then one at 5:00 I need to count the 9:00 as an In time, the 12:30 as an out time, the 3:00 as an In time the 5:00 as an out time.
I need to be able to give the business Time on the floor for each attendee (based on badge type, which will be joined up after I can break the data down in the correct way to look at this). I'm tryting to get some sort of matrix type of thing.
does that make since?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply