CODEWITHSUNDEEP
sqlsql-servert-sqlcount
eduarboy
eduarboy

Reputation: 53

Create table to count visits per hour

I need to count hoy many visits have an application when my customers create an account and show there info, so my customers can see how many visits they have

I have created a table to store integers but Im not sure if this is the right choice

CREATE TABLE tbHit(
intHit INT IDENTITY(1,1) NOT NULL,
intClient INT,
intYear INT,
intMonth INT,
intDay INT,
hr00 INT,
hr01 INT,
hr02 INT,
hr03 INT,
hr04 INT,
hr05 INT,
hr06 INT,
hr07 INT,
hr08 INT,
hr09 INT,
hr10 INT,
hr11 INT,
hr12 INT,
hr13 INT,
hr14 INT,
hr15 INT,
hr16 INT,
hr17 INT,
hr18 INT,
hr19 INT,
hr20 INT,
hr21 INT,
hr22 INT,
hr23 INT
)

Should I store all visits in the same table or shoul I create another table or another way to have all this hits?

Upvotes: 0

Views: 114

Answers (2)

Dudi Konfino
Dudi Konfino

Reputation: 1136

Use computed columns and split to date and time to simplify query at future

CREATE TABLE tbHit ( intHit    INT IDENTITY(1,1) NOT NULL
,                    intClient INT
,                    enter_date as convert(varchar(10),getdate(),113)
,                    enter_time as convert(varchar(10),getdate(),108) )

Upvotes: 0

benjamin moskovits
benjamin moskovits

Reputation: 5458

Every time they visit you should insert a row into a table like this:

create table hits (id1 identity, 
customer_id  int,
visited_on datetime)

There are date and time functions that will allow you to slice and dice hits in any angle you want(By day, hour, day of week) if you store it this way.

Upvotes: 2

Related Questions