Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

|ID|Plate|Date_in|Time_in|Date_out|time_out|

There are also two other tables, called "tblin_buffer" and "tblout_buffer" which have the following fields respectively:

|Plate|Date_in/out|Time_in/out|

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.

Additional information:

Previous Insert ideas for IN data:

Original idea:

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;

SQL command for inserting contents of tblout_buffer

SQL command for inserting contents of tblin_buffer

What do you recommend as a solution to this? Your input is greatly appreciated.

share|improve this question
    
Have you looked into INSERT ... ON DUPLICATE (dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html)? It can do an INSERT or UPDATE if the record already exists. There's also a REPLACE INTO which does an insert or delete` and insert. dev.mysql.com/doc/refman/5.0/en/replace.html –  FrustratedWithFormsDesigner Dec 11 '13 at 18:35
    
This looks promising, but how would you recommend that I implement it in my scenario? (apologies, I'm still very much a rookie with SQL and Databases) –  almg Dec 12 '13 at 9:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.