Code: Select all
| type | varchar(8)
| parent_id | int(32) unsigned
| variables | text
| values | text
| user_id | int(10) unsigned
| timestamp | datetime
| id | int(36) unsignedvariables are the fields, values are values.
Query input: user_id, type, time_start, time_end
Query output: list of parent_ids added and updated during the time period, with notification of whether it was an add or an update.
What I've got so far:
Code: Select all
SELECT DISTINCT(parent_id), ( SELECT COUNT(*) FROM tracking AS t WHERE type = 'property' AND parent_id = tracking.parent_id AND timestamp < tracking.timestamp ) > 0 AS new FROM tracking WHERE type = 'property' AND timestamp > NOW()-INTERVAL 30 DAY AND user_id = 1 LIMIT 10;