Help with query - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query

Author

Topic

panayiotis
Starting Member

16 Posts

Posted - 2008-01-29 : 17:47:47

Hello,

I have these two tables below:

CREATE TABLE [dbo].[Page](
[PageID] [int] NOT NULL,
[TrackerID] [int] NOT NULL,
[PageName] [varchar](250) NOT NULL,
[Datetime] [datetime] NOT NULL,
CONSTRAINT [PK_Pages] PRIMARY KEY CLUSTERED
(
[PageID] ASC
)
) ON [PRIMARY]

CREATE TABLE [dbo].[Tracker](
[TrackerID] [int] NOT NULL,
[HostAddress] [varchar](25) NOT NULL,
[HostName] [varchar](250) NULL,
[Referrer] [varchar](250) NULL,
[VisitCount] [int] NOT NULL,
[UserAgent] [varchar](120) NOT NULL,
[Platform] [varchar](15) NOT NULL,
[Browser] [varchar](15) NOT NULL,
[ScreenSize] [varchar](15) NOT NULL,
[ISCrawler] [bit] NOT NULL,
[Javascript] [bit] NOT NULL,
CONSTRAINT [PK_Tracker] PRIMARY KEY CLUSTERED
(
[TrackerID] ASC
)
) ON [PRIMARY]

I am using them to store data relating visits to a site. I need a querry that will return the number of unique visits per day and the total number of pages viewed. Also these must not include crawlers which are identified by IsCrawler in Tracker table.

The result should be something like:

"Unique Visits" "Pages Viewed" "Day"
100 150 1
100 150 2
100 150 3
100 150 4
100 150 5
0 0 0
0 0 0
.....

The closer querry i came up with is:

SELECT COUNT(*) AS 'Pages', DAY(DATETIME) AS 'Day' FROM PAGE
WHERE MONTH(DATETIME)=MONTH(GETDATE()) AND YEAR(DATETIME)=YEAR(GETDATE())
GROUP BY DAY(DATETIME)
ORDER BY DAY(DATETIME) ASC

Which only gives the total pages viewed per day.

I hope all makes sence.

Thanks in advance for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 17:59:52

[code]SELECT COUNT(DISTINCT t.HostAddress),
COUNT(p.PageName),
DATEADD(DAY, DATEDIFF(DAY, '19000101', p.Datetime), '19000101')
FROM Page AS p
INNER JOIN Tracker AS t ON t.TrackerID = p.TrackerID
WHERE p.Datetime >= DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')
AND p.Datetime < DATEADD(MONTH, DATEDIFF(MONTH, '18991231', GETDATE()), '19000101')
GROUP BY DATEADD(DAY, DATEDIFF(DAY, '19000101', p.Datetime), '19000101')
ORDER BY DATEADD(DAY, DATEDIFF(DAY, '19000101', p.Datetime), '19000101')[/code]

E 12°55'05.25"
N 56°04'39.16"

panayiotis
Starting Member

16 Posts

Posted - 2008-01-29 : 18:30:15

THANK YOU VERY MUCH ... Absolutely perfect!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources