sql server 2005 - query syntax

Hi,
I am trying to list all the TreeCodeIds which has CurrenStatusID='LIVE', StatusID='CODE_CRATED' and If the code is created in...

1st quarter ( January, February or March) 2009, then the expiry is the last day of April 2011

2nd quarter (April, May or June) 2009, then the expiry is the last day of July 2011

3rd quarter (July, August or September) 2009, then the expiry is the last day of October 2011

4th quarter (October November or December) 2009, then the expiry is the last day of January 2012

I have attached the partial query which will get 2 years old records - i want to apply quarter criteria to it. Please can someone help?

Thanks
TreeCertificate
------------------
TreeCodeID	 CurrentStatusId
12345   	 LIVE
23241		 REGISTERED
23423		 LIVE
      
	
TreeCertificateHistory
-------------------------
TreeCodeId	 StatusID	  TreeHistDateAdded
12345   	 CODE_CREATED     14/06/2010		 
23241		 REGISTERED	  14/12/2010	
23423		 CODE_CREATED	  19/04/2009
23333		 CODE_CREATED	  02/10/2009


SELECT TreeCertificate.TreeCodeID
		FROM TreeCertificate, TreeCertificateHistory
		WHERE CurrentStatusID = 'LIVE'
		  AND TreeCertificate.TreeCodeID = TreeCertificateHistory.TreeCodeID
	      AND TreeHistDateAdded < DATEADD(month, -25, GETDATE())
		  AND StatusID = 'CODE_CREATED'

Open in new window

dairaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you surely know about the datepart function:

DATEPART(QUARTER, yourfield) = 1  -- to know if the date is in 1st quarter
DATEPART(YEAR, yourfield) = 2011  -- obvious, I would say.

now, you might also want to read this article:

http://www.experts-exchange.com/A_1499.html
0
dairaAuthor Commented:
Hi
Thanks for the reference
The correct datetime stored in the database is

>> 2009-08-12 14:52:56.002

Yes I have been trying to figure the correct syntax / logic to use in my SQL statement.

I am strill struggling a bit - please can you help?

thanks
0
ThomasianCommented:
AND TreeHistDateAdded < SELECT DATEADD(quarter,DATEDIFF(quarter,0,DATEADD(month,-1,GETDATE()))-8,0)

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

ThomasianCommented:
Sorry, had an extra SELECT in the code
AND TreeHistDateAdded < DATEADD(quarter,DATEDIFF(quarter,0,DATEADD(month,-1,GETDATE()))-8,0)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dairaAuthor Commented:
Thanks Thomasian

That seems to have worked. May be I dont need to include the following in my statement

 AND TreeHistDateAdded < DATEADD(month, -25, GETDATE())

as your suggested will check last 8 quarters ?

Thank you
0
ThomasianCommented:
Yes, you can replace that with the query condition I posted. It return all records that has expired based on your criteria for expiration.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.