New Row in table for each Hour

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

Transact-SQL (2000)

New Row in table for each Hour

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-04-19 : 00:20:24

Hi,

Currently I log a new row in the database for each day using:

DECLARE @today AS DATETIME

SET @today = convert(varchar,month(getdate())) + '/' + convert(varchar,day(getdate())) + '/' + convert(varchar,year(getdate()))

Q#1 I now want to create a single row for each Hour. I need something that is the fastest possible way since this is a query that is run ALLOT in my application.

Before inserting into the table, if first check if the row exists, if so, I increment a counter in the table.

So I do:

IF EXISTS (Select NULL from MyTable Where today = @today)
BEGIN
UDPATE ...
END
ELSE
BEGIN
INSERT myTable (today, ...)
values (@today, ...)
END

Q#2 any ideas on speeding this query up would be greatly appreciated also.

X002548
Not Just a Number

15586 Posts

Posted - 2004-04-19 : 09:35:02

Every hour?

Most things are event driven...why do you want to do this?

Where do you want to do this?

Scheduled job?

Trigger?

Why?

Brett

8-)

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2004-04-19 : 13:16:17

No No! :)

Example: Each page view of a website I hit the database. Instead of creating 1 row per page view, I create 1 row per hour. If the row exists I update the Count Field, if not then I create a row with the timestamp of the day and hour etc.

So I need to @today filed to be initialized with the timestamp and to use in the where clause (see my original post)

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-04-19 : 13:31:52

Will this work for you?

DECLARE @OneHourAgo datetime
SET @OneHourAgo = DATEADD(hour, -1, GETDATE())

--See if the row does not exists. If not add it to the database.
IF(Not EXISTS(SELECT *
FROM MyTable WHERE MyTable.Today > @OneHourAgo))
BEGIN
INSERT INTO MyTable (Today,...)
VALUES(@OneHourAgo,...)
END

--Increment the count of the current hour by 1.
UPDATE MyTable
SET MyTable.NumberOfHits = MyTable.NumberOfHits + 1
WHERE MyTable.Today >= @OneHourAgo

Dustin Michaels

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-04-20 : 02:41:02

I seriously think it will be a lot easier and more manageable to have one row per hit. And getting results out of it should be no problem at all:


SELECT DATEPART(hh, visit_date), COUNT(*)
FROM Hits
WHERE visit_date = @VisitDate
GROUP BY DATEPART(hh, visit_date)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources