Calculate the age of the ticket (was "Query")

Dev Shed Forums Sponsor:

November 1st, 2011, 02:40 PM

cmorse723

Registered User

Join Date: Nov 2011

Posts: 8

Time spent in forums: 4 h 56 m 58 sec
Reputation Power: 0

Calculate the age of the ticket (was "Query")

So i'm trying to create a query that currently looks up a name of an asset from a specific table (Cluster_Owner_Lookup) from the original data table (Ticket_Data). I got that to work, that is the inner query (). Now where i'm having trouble is the outer query where i need to calculate the age of the ticket. That data is in the Ticket_Data.

I'm quite new at SQL language, i've taken a class on it before, but now this is my first real world application of it.

SELECT *
FROM Ticket_Data
WHERE DATEDIFF(dd, [Ticket_Data.Open_Time], [GETDATE( )]) AS Age
(SELECT DISTINCT Ticket_Data.Number, Cluster_Owner_Lookup.Cluster_Owner, Cluster_Owner_Lookup.Asset, Ticket_Data.Open_Time
FROM Ticket_Data, Cluster_Owner_Lookup
WHERE ((Ticket_Data.Asset)=[Cluster_Owner_Lookup].[Asset]))
ORDER BY Cluster_Owner_Lookup.Cluster_Owner
;

November 2nd, 2011, 04:42 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

Code:

SELECT Cluster_Owner_Lookup.Cluster_Owner
     , Ticket_Data.Number
     , Ticket_Data.Asset
     , Ticket_Data.Open_Time
     , DATEDIFF(dd,Ticket_Data.Open_Time,GETDATE()) AS Age
  FROM Ticket_Data
INNER
  JOIN Cluster_Owner_Lookup
    ON Cluster_Owner_Lookup.Asset = Ticket_Data.Asset
ORDER 
    BY Cluster_Owner_Lookup.Cluster_Owner

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

November 2nd, 2011, 07:23 AM

cmorse723

Registered User

Join Date: Nov 2011

Posts: 8

Time spent in forums: 4 h 56 m 58 sec
Reputation Power: 0

Quote:

Originally Posted by r937

Code:

SELECT Cluster_Owner_Lookup.Cluster_Owner
     , Ticket_Data.Number
     , Ticket_Data.Asset
     , Ticket_Data.Open_Time
     , DATEDIFF(dd,Ticket_Data.Open_Time,GETDATE()) AS Age
  FROM Ticket_Data
INNER
  JOIN Cluster_Owner_Lookup
    ON Cluster_Owner_Lookup.Asset = Ticket_Data.Asset
ORDER 
    BY Cluster_Owner_Lookup.Cluster_Owner

r937 thanks for the reply!, i tried to just run the query and it comes up with undefined function 'GetDate' in expression.

I also forgot to mention that there may be multiple tickets for one asset, so idk if an inner join would be the best for me.

November 2nd, 2011, 07:35 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

Quote:

Originally Posted by cmorse723

...and it comes up with undefined function 'GetDate' in expression.

may i please see the exact error message

November 2nd, 2011, 07:42 AM

cmorse723

Registered User

Join Date: Nov 2011

Posts: 8

Time spent in forums: 4 h 56 m 58 sec
Reputation Power: 0

Quote:

Originally Posted by r937

may i please see the exact error message

I actually just got it:

SELECT Cluster_Owner_Lookup.Cluster_Owner
, Ticket_Data.Number
, Ticket_Data.Asset
, Ticket_Data.Open_Time
, DATEDIFF("d",Ticket_Data.Open_Time,NOW()) AS Age
FROM Ticket_Data
INNER
JOIN Cluster_Owner_Lookup
ON Cluster_Owner_Lookup.Asset = Ticket_Data.Asset
ORDER
BY Cluster_Owner_Lookup.Cluster_Owner;

Thanks for the help!!!!

November 2nd, 2011, 07:46 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

so you're ~not~ using microsoft sql server after all (the forum you posted in)...

November 2nd, 2011, 07:50 AM

cmorse723

Registered User

Join Date: Nov 2011

Posts: 8

Time spent in forums: 4 h 56 m 58 sec
Reputation Power: 0

Ooops, I'm using Access as of right now.

November 2nd, 2011, 08:14 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

Quote:

Originally Posted by cmorse723

Ooops, I'm using Access as of right now.

no problem, i know how easy it is to get confused by the names of our forums here

November 3rd, 2011, 10:52 AM

cmorse723

Registered User

Join Date: Nov 2011

Posts: 8

Time spent in forums: 4 h 56 m 58 sec
Reputation Power: 0

Another question, I wrote this ontop of the pre-existing code, Now what i'm asking it for to do is lookup another value after it calculates the age and be able to put a "range" to it. Should i be using the () to tell it i want this to run after the first 6 lines?

Code:


SELECT Cluster_Owner_Lookup.Cluster_Owner, Ticket_Data.Number, Ticket_Data.Asset, Ticket_Data.Open_Time, DateDiff("d",Ticket_Data.Open_Time,Now()) AS Age
FROM Ticket_Data INNER JOIN Cluster_Owner_Lookup ON Ticket_Data.Asset = Cluster_Owner_Lookup.Asset
(SELECT AGE_LOOKUP.RANGE FROM AGE_LOOKUP INNER JOIN AGE_LOOKUP ON CLUSTER_OWNER_QUERY.AGE = AGE_LOOKUP.AGE)
ORDER BY Cluster_Owner_Lookup.Cluster_Owner;

#10

November 3rd, 2011, 12:07 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,353

Time spent in forums: 3 Months 1 Week 2 Days 4 h 17 m 19 sec
Reputation Power: 4140

Code:

SELECT Cluster_Owner_Lookup.Cluster_Owner
     , Ticket_Data.Number
     , Ticket_Data.Asset
     , Ticket_Data.Open_Time
     , DATEDIFF("d",Ticket_Data.Open_Time,NOW()) AS Age
     , age_lookup.range
  FROM (
       Ticket_Data
INNER
  JOIN Cluster_Owner_Lookup
    ON Cluster_Owner_Lookup.Asset = Ticket_Data.Asset
       )
INNER
  JOIN age_lookup 
    ON age_lookup.age =         
       DATEDIFF("d",Ticket_Data.Open_Time,NOW())    
ORDER 
    BY Cluster_Owner_Lookup.Cluster_Owner;

Calculate the age of the ticket (was "Query")

Developer Shed Advertisers and Affiliates