I am currently working on a database that aims to provide information regarding the amount of time people stay in a company's car-park premises. This is done using a table called "tbl_stay" which has the following fields:
There are also two other tables, called "tblin_buffer" and "tblout_buffer" which have the following fields respectively:
There are two Number-plate reading cameras pointed at the entrance and exit that produce a file when a plate is read. There is a program running on the database server machine that detects when a file is created then loads the data into these buffer tables
The intention is for data in these tables to be transferred into the main table with an
Insert command for the IN data and an
Update command for the OUT data. These commands should function for the most recent entries in the buffer tables and ignore the others that have already been transferred into the main tbl_stay.
I have tried a few methods suggested in the past that have come close to what I have been aiming for, but haven't been quite right. most of the time this is because days and/or times have not been matching up properly.
This insert/update method must be used because of an active query that gets run on the table that requests a list of "vehicles on site", indicated by vehicles that have an Entrance time but do not have an exit time.
Previous Insert ideas for IN data:
insert into tbl_stay (Plate,Date_in,Time_in) select Plate,Date_in,Time_in from tblin_buffer where not exists ( select * from tbl_stay where (tblin_buffer.Plate=tbl_stay.plate and tblin_buffer.Date_in=tbl_stay.Date_in and tblin_buffer.Time_in=tbl_stay.Time_in))
second idea, following from some guidance from another question. this was good, but it was copying the entire contents of tblin_buffer each time. i got around this by adding the "Where" clause at the bottom.:
insert into tbl_stay (Plate,date_in,time_in,rank_in) SELECT a.plate, a.date_in, a.time_in,rank FROM ( SELECT x.plate, x.date_in, x.time_in, COUNT(*) rank FROM tblin_buffer x JOIN tblin_buffer y ON y.plate = x.plate AND ((y.date_in < x.date_in) OR (y.date_in = x.date_in AND y.time_in <= x.time_in)) GROUP BY x.plate, x.date_in, x.time_in) a where a.plate = "<PLATE VALUE>" and a.time_in like"%<TIME VALUE>%"
previous Update ideas for OUT data
Original idea, this ended up copying the first exit time into all following entries of the same plate exiting:
update tbl_stay join tblout_buffer on tblout_buffer.plate=tbl_stay.plate set tbl_stay.date_out=tblout_buffer.Date_out, tbl_stay.time_out=tblout_buffer.time_out where tbl_stay.date_in=tblout_buffer.date_out
second idea, following on from guidance from another question. this solution came close, but some cases return incorrect dates, if the plate gets read multiple times in a row.
update tbl_stay JOIN ( SELECT distinct x.plate, x.date_out, x.time_out, COUNT(*) rank FROM tblout_buffer x JOIN tblout_buffer y ON y.plate = x.plate AND ((y.date_out < x.date_out) OR (y.date_out = x.date_out AND y.time_out <= x.time_out)) GROUP BY x.plate, x.date_out , x.time_out ) b ON b.plate = tbl_stay.plate AND b.rank = tbl_stay.rank_in set tbl_stay.date_out=b.date_out, tbl_stay.time_out=b.time_out, tbl_stay.rank_out=b.rank;
What do you recommend as a solution to this? Your input is greatly appreciated.