Help with SQL - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SQL

Author

Topic

internsp
Starting Member

1 Post

Posted - 2010-12-14 : 02:45:03

Hi

Currently I have a table call tblNames, with only 3 ID inside, primaryKeyID, nameID and dateID.

Each nameID has dateID.

E.g.
1, James, 12.01.2001
2, James, 15.01.2005
3, James, 16.01.2010
4, Mary, 12.01.2001
5, Mary, 12.01.2003

How do I display names with dates that are not within the last 3 year? Meaning I do not want james to be display.

Thanks.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-12-14 : 04:04:15

Is this ?

Declare @Test table
(Id int primary key,
NameId varchar(50),
dateId datetime)

Insert into @Test
Select 1, 'James', '2001-12-01' union all
Select 2, 'James', '2005-01-15' union all
Select 3, 'James', '2010-01-16' union all
Select 4, 'Mary', '2001-01-12' union all
Select 5, 'Mary', '2003-01-12'

Select distinct NameID from @Test where nameid not in
(select Nameid from @test where dateid >dateadd(yy,-3, getdate()))

kashyap.2000
Starting Member

22 Posts

Posted - 2010-12-14 : 04:26:35

select * from [Put your table name here] where datediff(mm,dateID,getdate())<=36

Failures will either break you or will make you a better perosn....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources