Selecting Date Ranges - SQL Server Forums

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

Other Forums

Other Topics

Selecting Date Ranges

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-23 : 10:37:38

Daniel Hewes writes "Hello,

I am currently building a stats system for my site and haven't a clue on how to select date ranges, e.g.

Hits this week: 394
Hits last week: 456

dates are sorted in a "date" field in MySql.

Have tried searching for answers but to no avail. Please help!
many thanx."

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-06-23 : 11:07:12

Here would be a SP In SQL

Declare @currweek Int , @Prevweek Int, @curryear Int,@Prevweekyear int

Select @currweek = Datepart(Week, Getdate()),@curryear = Year(getdate())
Select @Prevweek = Datepart(Week,DAteadd(week,-1,Getdate())),
@Prevweekyear = Datepart(Year,DAteadd(week,-1,Getdate()))

Select Display = 'Hits_this_week', Hit_Count = Count(*)
Into #Temptable
From YourTable
Where ( Datepart(Week,hitdate) = @currweek ) AND Datepart(Year,hitdate)= @curryear

Insert into #Temptable
(display,Hit_Count)
Select Display = 'Hits_LAST_week', Hit_Count = Count(*)

From YourTable
Where ( Datepart(Week,hitdate) = @Prevweek ) AND Datepart(Year,hitdate)= @Prevweekyear

Select Display,Hit_Count
From #Temptable

Drop table #Temptable

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources