CODEWITHSUNDEEP
sqlteradata
Errol Green
Errol Green

Reputation: 49

Return line with oldest and latest timestamp

I'm working on a sql script right now inside TERADATA and what Im trying to get it so when I search for a specific USERID it will return the USERID and have the OLDEST and LATESTRSLVD_DTTM timestamp associated with that USERID.

USERID     RSLVD_DTTM
USER1      12/06/2012  
USER1      08/01/2013  
USER1      06/11/2012  
USER1      03/04/2012

As you can see, it shoots out a bunch of dates... I'd like it to be only the oldest date and the latest date.

This is what Im trying to get it to look like, Also, im trying to get it joined into one line if possible. Im fairly new with TERADATA and could use some help.

    USERID     RSLVD_DTTM(OLDEST) RSLVD_DTTM(LATEST)
USER1      03/04/2012         08/01/2013

Here is my query, 

select 
 USERID,
 RSLVD_DTTM
from 
symmktg.VREMEDY_SYM_TICKET
where 
USERID IS IN ('USER1')

Please keep in mind im a very new to this area, please explain your awnser.

Any help is appreciated thanks!

Upvotes: 0

Views: 4455

Answers (1)

zimdanen
zimdanen

Reputation: 5626

You should be able to do something like this:

SELECT USERID, MIN(RSLVD_DTTM) MinDate, MAX(RLSVD_DTTM) MaxDate
FROM symmktg.VREMEDY_SYM_TICKET
GROUP BY USERID

This will give you the data for every user. If you just need the data for one user at a time, this should do it:

SELECT USERID, MIN(RSLVD_DTTM) MinDate, MAX(RLSVD_DTTM) MaxDate
FROM symmktg.VREMEDY_SYM_TICKET
WHERE USERID = 'USER1'

Doing an IN makes me think you might have multiple users; if that's the case, then add the WHERE clause to the first example.

Upvotes: 2

Related Questions