Donate. I desperately need donations to survive due to my health

Get paid by answering surveys Click here

Click here to donate

Remote/Work from Home jobs

Access SQL Remove duplicates from 5 minutes snapshot database - Challenge

CHALLENGE:

So I have a database which is a 5 minutes snapshot of live data. Data will be saved in an Access table every 5 minutes with all Events active at that time, being "Building" the same, only "Event" can be different, and "Time" will be the Now() at the time when snapshot is taken.

What I need is to create a Query against that table which will store every different building, every different event but only one "Time" (the oldest one) unless there is a difference greater than 5 minutes between oldest and another one. This is because in case there are more than 5 minutes, it means that at some point when the snapshot was taken, it was not active and therefore we must count it differently.

So being this the Table Snapshot:

Buildig----Time----Event    
France----12:00----B    
Spain-----13:05----A    
Spain-----13:10----A    
Spain-----13:15-----A    
Italy------15:00-----B    
Italy------15:05-----B    
Italy------15:10-----C    
Italy------15:15-----C    
USA------16:00------A    
USA------16:05------A    
USA------17:00------A

This is the result I would expect from the Query:

Buildig----Time----Event    
France----12:00----B    
Spain-----13:05----A    
Italy------15:00-----B    
Italy------15:10-----C    
USA------16:00-----A    
USA------17:00-----A

France only one event so only one row. Spain one event which is repeated every 5 minutes so only one row. Italy 2 different events repeated 4 times because of 5 min difference, so 2 rows. USA 1 event but repetated with more than 5 minutes difference, so 2 rows.

So basically: eliminate duplicates on "Time" considering 5 minutes as same time and in "Event" for every building.

Comments