Just another tech site

T-SQL – trend per day, per hour in a day


Introduction

the use of the T-SQL date functions : DATEPART
Building a temporary table

SELECT DATEPART(year, '12:10:30.123')
    ,DATEPART(month, '12:10:30.123')
    ,DATEPART(day, '12:10:30.123')
    ,DATEPART(dayofyear, '12:10:30.123')
    ,DATEPART(weekday, '12:10:30.123');

Day trend

select 
datepart(dy, DateSurvey) AS DayOfYear,DateSurvey, XID 
into 
#allLogins2 
from 
Survey with (nolock) 
Where DateSurvey IS NOT NULL
AND VIP IS NULL
select 
DayOfYear, count(*) 
from 
#allLogins2
Group By DayOfYear
order By DayOfYear

Hour of Day trend

select 
datepart(hour, EventDate) AS hourofday, LoginNumber 
into 
#allLogins8 
from 
LogWebEvent with (nolock) 
where 
EventType = 50 
AND EventDate <= '20110605' AND EventDate > '20110604' 
select 
hourofday, count(*) 
from 
#allLogins8
Group By hourofday
order By hourofday

Reference

[1] http://msdn.microsoft.com/fr-fr/library/ms174420.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Tag Cloud

%d bloggers like this: