sql server 2005 - query syntax

Posted on 2011-10-03
Last Modified: 2012-05-12
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?

TreeCodeID	 CurrentStatusId
12345   	 LIVE
23423		 LIVE
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())

Open in new window

Question by:daira
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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:

    Author Comment

    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?

    LVL 22

    Expert Comment

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

    Open in new window

    LVL 22

    Accepted Solution

    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


    Author Comment

    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
    LVL 22

    Expert Comment

    Yes, you can replace that with the query condition I posted. It return all records that has expired based on your criteria for expiration.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now