Revive Ad Server raw SQL

revive_ad_server.sql

#Show all active campaigns
SELECT * FROM `rv_campaigns` WHERE `activate_time` <= now() and expire_time >= now()

#show all past campaigns
SELECT * FROM `rv_campaigns` WHERE `activate_time` <= now() and expire_time <= now()

#show all upcoming campaigns
SELECT * FROM `rv_campaigns` WHERE `activate_time` >= now()

#show campaigns with clients (advertiser) information
SELECT * FROM `rv_campaigns`
LEFT JOIN rv_clients on rv_campaigns.clientid=rv_clients.clientid

#shows banners with campaigns and client (advertiser) information
SELECT * FROM `rv_banners`
LEFT JOIN rv_campaigns on rv_banners.campaignid=rv_campaigns.campaignid
LEFT JOIN rv_clients on rv_campaigns.clientid=rv_clients.clientid

#shows statistics with banners, campaigns and clients (Advertisers)
SELECT * FROM `rv_data_summary_ad_hourly`
LEFT JOIN rv_banners ON rv_data_summary_ad_hourly.ad_id=rv_banners.bannerid
LEFT JOIN rv_campaigns on rv_banners.campaignid=rv_campaigns.campaignid
LEFT JOIN rv_clients on rv_campaigns.clientid=rv_clients.clientid

#shows statistics with zones banners, campaigns and clients (Advertisers)
SELECT * FROM `rv_data_summary_ad_hourly`
LEFT JOIN rv_zones ON rv_data_summary_ad_hourly.zone_id=rv_zones.zoneid
LEFT JOIN rv_banners ON rv_data_summary_ad_hourly.ad_id=rv_banners.bannerid
LEFT JOIN rv_campaigns on rv_banners.campaignid=rv_campaigns.campaignid
LEFT JOIN rv_clients on rv_campaigns.clientid=rv_clients.clientid