[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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

0
daira
Asked:
daira
  • 3
  • 2
2 Solutions
 
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now