dates

I have a view that shows me how many visits i have had on my website.
UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April (using
datename)), TheDay (Sunday (using datename),TheDate (10 (using datepart)).
The result is like this:
.......
23, 2005, April, Sunday, 10
So for April i so far has 10 records since it is April 10.
The table is like this:
CREATE TABLE [dbo].[T_PageStat] (
[IDStat] [int] IDENTITY (1, 1) NOT NULL ,
[DateRegistered] [datetime] NULL ,
[Counter] [numeric](18, 0) NULL ,
[IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
[BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
[LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY]
As you see i am collecting the date the visitor entered, a counter telling
me how many times this visitor entered, the IP, what kind of browser, and
finally the language the user has set in browser language. I use SPROC to
populate the table
I want to update my view so that i can get a record for every day in the
month even tho it is only April 10. The rest of the days will be 0
(11,12....)
Not sure how to do that so i was hoping for some help.
Any tip will be appreciated. I am using a SQL 2000 server
Best regards, Trond
The code for the view:
CREATE VIEW dbo.statUniquePrMonth
AS
SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
DateRegistered) AS TheDate
FROM dbo.T_PageStat
GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE())) AND
(DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
ORDER BY DATEPART(dd, DateRegistered)Hi
This may be easiest with a calander table e.g
http://www.aspfaq.com/show.asp?id=2519
You can then use an outer join to get all the days in the given month.
John
"Trond" <thoiberg@.broadpark.no> wrote in message
news:4258d99c$1@.news.broadpark.no...
>I have a view that shows me how many visits i have had on my website.
> UniqueVisits(number), TheYear(2005 (using datepart)), TheMonth(April
> (using datename)), TheDay (Sunday (using datename),TheDate (10 (using
> datepart)).
> The result is like this:
> .......
> 23, 2005, April, Sunday, 10
> So for April i so far has 10 records since it is April 10.
> The table is like this:
> CREATE TABLE [dbo].[T_PageStat] (
> [IDStat] [int] IDENTITY (1, 1) NOT NULL ,
> [DateRegistered] [datetime] NULL ,
> [Counter] [numeric](18, 0) NULL ,
> [IPAddress] [varchar] (50) COLLATE Danish_Norwegian_CI_AS NULL ,
> [BrowserData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL ,
> [LanguageData] [varchar] (200) COLLATE Danish_Norwegian_CI_AS NULL
> ) ON [PRIMARY]
> As you see i am collecting the date the visitor entered, a counter telling
> me how many times this visitor entered, the IP, what kind of browser, and
> finally the language the user has set in browser language. I use SPROC to
> populate the table
>
> I want to update my view so that i can get a record for every day in the
> month even tho it is only April 10. The rest of the days will be 0
> (11,12....)
> Not sure how to do that so i was hoping for some help.
> Any tip will be appreciated. I am using a SQL 2000 server
> Best regards, Trond
> The code for the view:
> CREATE VIEW dbo.statUniquePrMonth
> AS
> SELECT TOP 100 PERCENT COUNT(IDStat) AS UniqueVisits, DATEPART(YYYY,
> DateRegistered) AS TheYear, DATENAME(month, DateRegistered) AS TheMonth,
> DATENAME(dw, DateRegistered) AS TheDay, DATEPART(dd,
> DateRegistered) AS TheDate
> FROM dbo.T_PageStat
> GROUP BY DATEPART(YYYY, DateRegistered), DATENAME(month, DateRegistered),
> DATENAME(dw, DateRegistered), DATEPART(dd, DateRegistered)
> HAVING (DATEPART(YYYY, DateRegistered) = DATEPART(YYYY, GETDATE()))
> AND (DATENAME(month, DateRegistered) = DATENAME(month, GETDATE()))
> ORDER BY DATEPART(dd, DateRegistered)
>