Don't know if it is important: Debian Linux / MySQL 5.1
I have a table:
media_id int(8)
group_id int(8)
type_id int(8)
expiration date
start date
cust_id int(8)
num_runs int(8)
preferred_time int(8)
edit_date timestamp ON UPDATE CURRENT_TIMESTAMP
[.....]
id int(8)
Scenario: I run a report to show all media that was or will be active between two dates, say 10/01/2010 and 10/17/2010.
Now I can write a statement that will give me what i want but it seems like a bad way to do it.
SELECT * FROM media_a WHERE media_a.cust_id =3 AND (
( CAST( '2010-10-15' AS DATE ) BETWEEN media_a.start
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
OR ( CAST( '2010-10-16' AS DATE ) BETWEEN media_a.start
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
OR ( CAST( '2010-10-17' AS DATE ) BETWEEN media_a.start
AND DATE_ADD( expiration, INTERVAL 1 DAY ) )
) ORDER BY media_a.group_id, preferred_time
If someone knows of a cleaner more elegant way to do this and is willing to share, I'd be appreciative.
Thanks
Vertical