help with select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help with select statement

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-10-24 : 14:26:18

This query shows me all comments on transactions created.


Select Left(CRDATTIM,10) as KEY_DATE, MID(CRDATTIM,11,8) as KEY_TIME, RIGHT(CRDATTIM,6) as KEY_MILSEC, COMMDATTIM, COMMTEXT
From W21U999S

Here is my issue: There are multiple instances of CRDATTIMs (create date/time) I only want the ones with the latest COMMDATTIM (comment date/time) so that I'm displaying the latest comment.

COMMDATTIM would look like this: 2008-10-24-09.52.03.936206

any help

Thanks,

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 16:09:40

[code]Select Left(CRDATTIM,10) as KEY_DATE, MID(CRDATTIM,11,8) as KEY_TIME, RIGHT(CRDATTIM,6) as KEY_MILSEC, (select max(COMMDATTIM) from W21U999S where ID = z.ID) as LASTCOMMDATTIM, COMMTEXT
From W21U999S z
[/code]

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2008-10-24 : 19:38:09

Thanks for your help.

I got this to work for me..

Select Left(rh.CRDATTIM,10) as KEY_DATE, MID(rh.CRDATTIM,12,8) as KEY_TIME, RIGHT(rh.CRDATTIM,6) as KEY_MILSEC, rh.COMMDATTIM, rh.COMMTEXT
From W21U999S rh,
   (SELECT max(COMMDATTIM) as maxtime, CRDATTIM
    FROM W21U999S
    GROUP BY CRDATTIM) maxresults
WHERE rh.CRDATTIM = maxresults.CRDATTIM
AND rh.COMMDATTIM =maxresults.maxtime
AND rh.CODEM = 'M'

Select rh.KEY_DATE, rh.KEY_TIME, rh.KEY_MILSEC, rh.EVENT_DATTIM, rh.LASTNAME, rh.FIRSTNAME
From WA5U999S rh,
   (SELECT max(EVENT_DATTIM) as maxtime, KEY_DATE, KEY_TIME, KEY_MILSEC
    FROM WA5U999S
    GROUP BY KEY_DATE,KEY_TIME,KEY_MILSEC) maxresults
WHERE rh.KEY_DATE = maxresults.KEY_DATE
AND rh.KEY_TIME = maxresults.KEY_TIME
AND rh.KEY_MILSEC = maxresults.KEY_MILSEC
AND rh.EVENT_DATTIM =maxresults.maxtime
AND rh.STATCODE = 'PROCESSED'
AND rh.EVENTCODE = 'BIUPDATW'

SELECT KEY_DATE, KEY_TIME, KEY_MILSEC, COUNT(1) as "COUNTTXNS"
FROM WB5U999S
WHERE PRCCODE = 'T'
GROUP BY KEY_DATE, KEY_TIME,KEY_MILSEC;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources