daira
asked on
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
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'
Last Comment
SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
AND TreeHistDateAdded < SELECT DATEADD(quarter,DATEDIFF(quarter,0,DATEADD(month,-1,GETDATE()))-8,0)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
Yes, you can replace that with the query condition I posted. It return all records that has expired based on your criteria for expiration.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER
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