if record exsist all last x years

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

if record exsist all last x years

Author

Topic

hassanz25
Starting Member

6 Posts

Posted - 2009-09-30 : 02:49:47

Hi I want a query to get all the records who exsist in all last x years in my case x = 5.

THanks
Hassan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-30 : 03:07:30

5 year from today ?


where datecol >= dateadd(year, -5, getdate())

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-30 : 03:15:15

He means in ALL of the last x years not only one.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-30 : 03:24:12

Without some sample data and DDL, it's impossible to tell.

N 56°04'39.26"
E 12°55'05.63"

hassanz25
Starting Member

6 Posts

Posted - 2009-09-30 : 18:51:43

for example

if John made payment in 2009(current year), 2008,2007,2006,2005
then fetch his name

Contact table
contactID
name

payemnt table
contactID
Date
paymentID
amount

select contact.name, payment.amount if [contact.name] is paying for in all last five years(he/she made payment in all the years)

Thanks

quote:
Originally posted by Peso

Without some sample data and DDL, it's impossible to tell.

N 56°04'39.26"
E 12°55'05.63"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-01 : 00:51:52

if you are using SQL 2005 / 2008, you can make use of the ALL keyword

see http://msdn.microsoft.com/en-us/library/ms178543%28SQL.90%29.aspx

EDIT : wrong advice

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 01:41:44

Hi KH,
I would like to see the solution to this problem using the ALL keyword.
Can you give an approach please?

Thanks
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-01 : 03:23:21

Yes. I would like to see that also.

Must be the coffee i had this morn, wasn't thinking straight.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-01 : 03:27:43

this should work. If not, please nuke me


select	*
from	Contact c
where	(
		select 	count(distinct datepart(year, [Date]))
		from	payemnt x
		where	x.[contactID]	= c.[contactID]
		and	x.[Date]	>= '2005-01-01'
		and	x.[Date]	<= '2009-12-31'
	)	= 5

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 03:31:23

i would have done it this way


SELECT t.Name
FROM Contact t
INNER JOIN (SELECT ContactID
            FROM payemnt
            WHERE Date >= DATEADD(yy,DATEDIFF(yy,0,GETDATE())-5,0)
            AND Date <DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)
            GROUP BY ContactID
            HAVING COUNT(DISTINCT YEAR(PaymentDate))=5
            )t1
ON t1.ContactID=t.ContactID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 03:32:06

Ah...I'm late

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-01 : 03:38:41

shouldn't it be DATEADD(yy,DATEDIFF(yy,0,GETDATE())-4,0) ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 03:40:39

quote:
Originally posted by khtan

shouldn't it be DATEADD(yy,DATEDIFF(yy,0,GETDATE())-4,0) ?

KH
[spoiler]Time is always against us[/spoiler]

yes..from current back 4 years..nice catch

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-01 : 05:29:18

quote:
Originally posted by khtan

this should work. If not, please nuke me

select *
from Contact c
where (
select count(distinct datepart(year, [Date]))
from payemnt x
where x.[contactID] = c.[contactID]
and x.[Date] >= ~~'2005-01-01'~~ '20050101'
and x.[Date] <= ~~'2009-12-31'~~ '20091231'
) = 5

KH
[spoiler]Time is always against us[/spoiler]

Nobody want's to nuke you because you are very valuable to us!

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-01 : 06:06:55

[code]SELECT c.*
FROM Contact AS c
INNER JOIN (
SELECT ContactID
FROM Payment
WHERE Date >= DATEADD(YEAR, DATEDIFF(YEAR, '19040101', GETDATE()), 0)
AND Date < DATEADD(YEAR, DATEDIFF(YEAR, '18990101', GETDATE()), 0)
GROUP BY ContactID
HAVING COUNT(DISTINCT DATEPART(YEAR, Date)) = 5
) AS p ON p.ContactID = c.ContactID[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources