#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    8
    Rep 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
    ;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    8
    Rep Power
    0
    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.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    Originally Posted by cmorse723
    ...and it comes up with undefined function 'GetDate' in expression.
    may i please see the exact error message
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    8
    Rep Power
    0
    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!!!!
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    so you're ~not~ using microsoft sql server after all (the forum you posted in)...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    8
    Rep Power
    0
    Ooops, I'm using Access as of right now.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    8
    Rep 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;
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,149
    Rep Power
    4274
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo