March 2, 2012 at 8:43 am
Heh... so it's not possible that someone could enter an area and the battery on their cell phone gives out? It's also not possible that someone driving along enters an are and discovers they forgot to turn their cell phone and and promptly does so?
A good point, but my impression is that the way the software on the phone works is if they are at any latitiude / longitude within a 1/3rd mile radius of designated areas, an "enter" event will occur and then an "exit" event occurs when they leave. I'm pretty sure the events don't trigger only when traversing the perimeters of the the radiuses. So if they should suddently find themselves within the perimeter, the "enter" event would occur. I assume the same would happen if they suddenly found themselves outside of the perimeter (like say when their battery died and they later turn it on).
Do you mean you want the following???
Enter 1, record as Enter 1
Exit 1 and immediately Enter 2... no record
Exit 2, record as Exit 1.
Precisely. And since if it's programmed this way it would probably be easier to use exit 2's time when exit 1/enter 2/exit 2 all happen simultaneously, we can do that.
Heh... so long as that flexibility doesn't require full blown AI, probably not a problem but let's hammer out the basic requirements first.
I would think a simple DATEDIFF statement somewhere would suffice here instead of AI. But if you wanna give some rudimentary AI a crack, I won't tell you to reach for the sky when there are footprints on the moon. 🙂
March 2, 2012 at 9:00 am
Just noticed one of today's headline articles might be relavent to what we're trying to do.
http://www.sqlservercentral.com/articles/T-SQL/70807/
It instructs how to elimate duplicate rows using the PARTITION BY clause. Assuming we dropped [Event ID] and [Event Type] and then removed the seconds from the time, we could compare the records and when records had the same [Event Time] (to the minute), eliminate any in the middle and take the MIN and MAX records.
WE'd need to be able to know that the first record of a given day was an enter event though. And like I mentioned, I did see some where they entered right before midnight and exited after midnight the next day.
I've gotta change gears for a bit to other projects, but will try and read the article later today and try to code something. Although I'm sure whatever you can come up with will be better, but maybe by trying it myself and then seeing a better way to do it, I can learn something.
March 13, 2012 at 2:33 pm
I was able to arrive at a solution. Basically I ended up making about 7 temp tables and using CTEs twice to parse out the unneeded data. Thanks for showing the CTE and getting me pointed in the right direction.
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply