PHP/MySQL ->
Create sites vists report using dates-sequence table

Imagine that we recived web sites clicks statistics organized as a number of visitors per dates range, like this:
siteidfdatetdatevisitors_num
236581999-01-281999-03-04508
249911999-02-031999-05-291702
203801999-03-181999-06-30919

Our task is to use this data and create daily/monthly statistics of site's visiting. In order to do this we will use JOIN between recived report (dates ranges table) and dates sequence table created earlier:
USE colombo;

/* create tables and put in some data */

-- this table stores websites information
CREATE TABLE websites
(
   siteid INT NOT NULL,
   site_url VARCHAR(100) NULL,
   UNIQUE KEY websites_uniq_indx (siteid)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- insert data
INSERT INTO websites (siteid,site_url)
VALUES (20380,'http://www.artofthestorage.co.uk');

INSERT INTO websites (siteid,site_url)
VALUES (23658,'http://www.numberonesimplesale.com');

INSERT INTO websites (siteid,site_url)
VALUES (24991,'http://www.happyholydaysale.biz');

-- this table stores site's visits report
CREATE TABLE site_visits
(
   idno INT NOT NULL,
   repdate DATETIME NULL,
   siteid INT NULL,
   fdate DATETIME NULL,
   tdate DATETIME NULL,
   visitors_num INT NULL,
   UNIQUE KEY site_visits_uniq_indx (idno)
)ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- insert data
INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (101,'2000-01-05',23658,'1999-01-28','1999-03-04',508);

INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (102,'2000-01-16',24991,'1999-02-03','1999-05-29',1702);

INSERT INTO site_visits
(idno,repdate,siteid,fdate,tdate,visitors_num)
VALUES (103,'2000-01-23',20380,'1999-03-18','1999-06-30',919);




/*
Run stored procedure that creates dates sequence. Source code of the procedure is here
*/

CALL sp_init_dates ( '1999-01-01','1999-07-30');

/*
Now we just write join between tables, where essential condition is that date value from the sequence table have to fall between reported dates range.
Formula: (a.visitors_num / DateDiff(a.tdate,a.fdate)) gives us daily quantity of visits.
*/


SELECT b.site_url, Month(c.sdate) AS the_month,
SUM(a.visitors_num / DateDiff(a.tdate,a.fdate)) AS monthly_visits
FROM site_visits AS a, websites AS b, seq_dates AS c
WHERE a.siteid = b.siteid
   AND Year(c.sdate) = 1999
   AND (c.sdate BETWEEN a.fdate AND a.tdate)
GROUP BY b.site_url, Month(c.sdate)
ORDER BY b.site_url, Month(c.sdate)


The results will be as follows:
site_urlthe_monthmonthly_visits
http://www.artofthestorage.co.uk4265
http://www.artofthestorage.co.uk5273
http://www.artofthestorage.co.uk6265
http://www.happyholydaysale.biz2384
http://www.happyholydaysale.biz3458
http://www.happyholydaysale.biz4444
http://www.happyholydaysale.biz5429



sqlexamples.info