Interval in select - 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

General SQL Server Forums

New to SQL Server Programming

Interval in select

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 02:47:18

I have a table that I want to count occurances based on different intervals, how do I do this?

create table #table1
(
id int identity,
title varchar(20),
myYear date
primary key(id)
)

insert into #table1 (title, myYear) values('title1', '1992-04-14')
insert into #table1 (title, myYear) values('title1', '1995-01-01')
insert into #table1 (title, myYear) values('title2', '1994-01-01')
insert into #table1 (title, myYear) values('title3', '1997-10-01')
insert into #table1 (title, myYear) values('title4', '2009-06-01')
insert into #table1 (title, myYear) values('title3', '1991-10-11')
insert into #table1 (title, myYear) values('title4', '1996-02-21')

Wanted output:
<1995 1995-1997 1997-1999 >2000
2 2 1 1

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 04:11:32

solved it using

SUM((CASE
WHEN myYear < '1995-01-01'
THEN 1
ELSE 0
END)) AS '<1995'
.
.
.
.
.

SD_Monkey
Starting Member

38 Posts

Posted - 2010-06-17 : 04:42:15

[code]
Select
case when Yrdefine < '1995' then YrCount else 0 end as '<1995'
case when Yrdefine >= '1995' and Yrdefine < '1997' then YrCount else 0 end as '1995-1997'
case when Yrdefine >= '1997' and Yrdefine < '1999' then YrCount else 0 end as '1997-1999'
case when Yrdefine >= '2000' then YrCount else 0 end as '>2000'
from
(Select count(year(myYear)) as YrCount,Year(myYear) as Yrdefine from table1 tb1 group by Year(myYear)) tempTable[\code]

hope this can help....

A maze make you much more better

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources