Counting events from a log for days when there are no events

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 2000 Forums

SQL Server Development (2000)

Counting events from a log for days when there are no events

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-10-27 : 08:00:08

Ian writes "I am building a report that tracks the number of click-thru events for an advertising campaign, and then charts the number of click-thru's per day.

The parameters we will be using are "campaign start date" and "number of days to view" For example, 30,60 or 90 days from Campaign Start. We may also need to work backwards from "Todays Date".

Anyway, I've got a query that returns HITS PER DAY, but it only shows days for which we have data... and I need to see a ZERO count on those days.

Here's an example output as it stands;

Date HITS
1-Oct-2006 100
2-Oct-2006 29
4-Oct-2006 1046
5-Oct-2006 587

So you can see that because there's no clicks on 3-Oct-2006, the count doesn't show up. I need it to look more like;

Date HITS
1-Oct-2006 100
2-Oct-2006 29
3-Oct-06 0
4-Oct-2006 1046
5-Oct-2006 587

Any suggestions you can provide would be MOST appreciated!!
Here's some sample SQL to show where I'm coming from;

CREATE TABLE [dbo].[ClickStats](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[EventType] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LinkID] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Cell] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateTime] [datetime] NULL CONSTRAINT [DF_ClickStats_DateTime] DEFAULT (getdate()),
[ClientIP] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_ClickStats] PRIMARY KEY CLUSTERED

------------------------------------------------------

SELECT ClickStats.EventType,CAST(CONVERT(VARCHAR(10), ClickStats.DateTime, 112) AS DATETIME) As Dates,
COUNT(LinkID) as HITS FROM ClickStats
WHERE LinkID = 1
GROUP BY EventType,CAST(CONVERT(VARCHAR(10), ClickStats.DateTime, 112) AS DATETIME)
ORDER BY Dates DESC"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-27 : 08:00:34

http://www.sqlteam.com/item.asp?ItemID=3332

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-10-27 : 09:52:42

You can use the function on this link to generate a date table.

Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

CODO ERGO SUM

ian.ross@clickdm.com.au
Starting Member

1 Post

Posted - 2006-10-27 : 22:10:53

Thanks guys. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources