How To: Run Monthly Reports for Total # of Page Hits, & Page Name

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