In order to run reports to get the page name and total number of page hits by month, you can run the following query and adjust as necessary:
SELECT [PageName], count([PageName]) as totalHits
,SUM(CASE WHEN(Month([DateTime])=1 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as January2014
,SUM(CASE WHEN(Month([DateTime])=2 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as February2014
,SUM(CASE WHEN(Month([DateTime])=3 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as March2014
,SUM(CASE WHEN(Month([DateTime])=4 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as April2014
,SUM(CASE WHEN(Month([DateTime])=5 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as May2014
,SUM(CASE WHEN(Month([DateTime])=6 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as June2014
,SUM(CASE WHEN(Month([DateTime])=7 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as July2014
,SUM(CASE WHEN(Month([DateTime])=8 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as August2014
,SUM(CASE WHEN(Month([DateTime])=9 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as September2014
,SUM(CASE WHEN(Month([DateTime])=10 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as October2014
,SUM(CASE WHEN(Month([DateTime])=11 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as November2014
,SUM(CASE WHEN(Month([DateTime])=12 and Year([DateTime])=2013) THEN 1 ELSE 0 END) as December2014
FROM [PWTrafficMonitorEntries]
where datetime > '2014-01-01' and datetime < '2015-01-01'
group by pagename
order by totalHits desc
Comments
0 comments
Please sign in to leave a comment.