Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chricholson
Starting Member
6 Posts |
 Posted - 2010-03-23 : 12:18:58
|
| I have a logging system on place on my website which takes note of a users IP, the page they are viewing and the date it was recorded. I want to produce a set of results which shows how many pages each user (ip) visited on a set date.I have produced this:SELECT DISTINCT(ip), COUNT(page), dateRecorded FROM tblIPLog GROUP BY ip, dateRecordedwhich works (or I assume it will) but the problem I have is the dateRecorded field takes into account hours, minutes, seconds and milliseconds as well as year, month and day. This cannot be changed to just record the year, month and day.I need someway to group just the year, month and day parts so I can see which user visited on any particular day. It does not matter if the user (ip) appears twice so long as it is over different days. However for multiple page visits on the same day it should count it up as normal.I tried the WHERE LIKE statement but as I do not want to view a specific date I did not know what to put.Many thanks in advance,Chris |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 12:26:47
|
| [code]SELECT ip, COUNT(page), DATEADD(dd,DATEDIFF(dd,0,dateRecorded),0) AS DateOnly FROM tblIPLog GROUP BY ip, DATEADD(dd,DATEDIFF(dd,0,dateRecorded),0)[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
chricholson
Starting Member
6 Posts |
Posted - 2010-03-23 : 12:48:03
|
| Perfect as usual! Many thanks to you! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 13:01:53
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Grouping by part of a column string