Sql Query to Retrive Archive Data

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)

Sql Query to Retrive Archive Data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-05 : 10:44:45

Lokendra writes "I have a Blog Table which contain following Blog Details .

Table: Blog

UserId | BlogId | BlogDetail | CreatedDate
1 1 xyz 12/23/2007
1 2 fdf 12/23/2007

The data is about 10000 rows and
according to above structure and sample data I want to use sql so that I can get the Data in Following form

Day Count
Wednesday 30
Tuesday 20
Monday 30
LastWeek 40
Two Weeks Ago 55
Last month 5
1 year Ago 200"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-03-05 : 11:44:54

[code]
declare @today datetime
select @today = DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)
select @today

select sum(case when CreatedDate >= @today then 1 else 0 end) as TodayCount,
sum(case when CreatedDate >= @today-1 and CreatedDate < @today then 1 else 0 end) as OneDayAgoCount,
-- this is generic for interval from N to M days ago
sum(case when CreatedDate >= @today-N and CreatedDate < @today-N+M then 1 else 0 end) as NDaysAgoCount
from YourTable
[/code]

Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources