troubleshooting Question

Need to create SQL query with time filer

Avatar of atoncelli
atoncelli asked on
Microsoft SQL Server
6 Comments1 Solution639 ViewsLast Modified:
Hello,
 I have running ClearQuest on Windows and I am trying to create a query that has a time filter.  In a nutshell, I want to query for all the defects that are not closed and the ones that are closed but only in the last 15 days.  I tried to create the query but I can get it to work only if I use a specific day and time (ts).  Here are my queries.

This one works fine:

select distinct T1.dbid,T1.id,T1.title,T2.name from Defect T1,statedef T2,project T4 where T1.state = T2.id and T1.project = T4.dbid and (T1.dbid <> 0 and ((T4.name = 'Project 1' and ((T2.name = 'Closed' and T1.closed_date >= {ts '2008-05-12 04:00:00'}) or ((T2.name <> 'Closed' or T1.state = 0)))))) order by T1.id ASC

But if I change the time to use sysdate it doesn't work anymore:

select distinct T1.dbid,T1.id,T2.name, T1.title from Defect T1,statedef T2,project T3 where T1.state = T2.id and T1.project = T3.dbid and (T1.dbid <> 0 and (( ( (T2.name <> 'Closed' or T1.state = 0) or (T2.name = 'Closed' and T1.closed_date >(sysdate - 15 ))and T3.name = 'Project 1'))) order by T1.id ASC

Any suggestions?
Thanks
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros