Fairly basic question, I think

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 2005 Forums

Transact-SQL (2005)

Fairly basic question, I think

Author

Topic

MikeDevenney
Starting Member

20 Posts

Posted - 2008-05-28 : 12:45:25

I have a table with rate histories that we recieve three times a day. I want to write a query that returns all fields for the row with the latest receipt time. ie - we get rates at 10 AM, 1 PM and 4 PM and I want to return the row from 4 PM. The arrival time is stored down to the seconds so I can't set an exact critera. I'm trying to so something like this...

Select *
from ratehistory
where ratehistoryid in (Select ratehistoryid, max(reciept) from ratehistory group by Cusip, Date(reciept))

Mike Devenney

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 12:51:02

[code]Select t.*
from ratehistory r
INNER JOIN
(Select Cusip, max(reciept) as maxdate from ratehistory
group by Cusip)t
on t.Cusip=r.Cusip
and t.maxdate=r.reciept[/code]

MikeDevenney
Starting Member

20 Posts

Posted - 2008-05-28 : 12:53:24

Awesome! Can I group on more than just the Cusip column? BEcause I need the latest load for each day I have to group by the receipt column as well.

Mike Devenney

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 12:55:45

quote:
Originally posted by MikeDevenney

Awesome! Can I group on more than just the Cusip column? BEcause I need the latest load for each day I have to group by the receipt column as well.

Mike Devenney

Didnt get that. latest load? Can you explain what that means?

MikeDevenney
Starting Member

20 Posts

Posted - 2008-05-28 : 13:15:09

Sorry, slipped from DB logic into business logic for a second there. The receipt column is a date which I will use to group rates received for a cusip on a given day, from that grouping I want to get the max of the timeReceived column. (Sorry left a few details out of the first post.)

Mike Devenney

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-28 : 13:25:35

quote:
Originally posted by MikeDevenney

Sorry, slipped from DB logic into business logic for a second there. The receipt column is a date which I will use to group rates received for a cusip on a given day, from that grouping I want to get the max of the timeReceived column. (Sorry left a few details out of the first post.)

Mike Devenney

may be this:-

Select t.* 
from ratehistory r
INNER JOIN
 (Select Cusip,receipt, max(timereceived) as maxtime from ratehistory 
group by Cusip,receipt)t
on t.Cusip=r.Cusip
and t.receipt=r.receipt
and t.maxtime=r.timereceived

MikeDevenney
Starting Member

20 Posts

Posted - 2008-05-28 : 15:41:37

Tried for a while and I'm not getting what I expected. I've gone back over my question and realize that without all the info it will be hard to answer. What I want to return is one row per Cusip per day (DateLoaded) that is the max TimeLoaded value for that day.

Sample data from RateHistory (pertinent fields only)
The forum formatting is stripping out my tabs so I've pipe delimited the columns.
HistID|Cusip|DateLoaded|TimeLoaded
84|047772-CZ9|9/7/2007|16:30
73|047772-CZ9|9/7/2007|13:30
65|047772-CZ9|9/7/2007|10:30

Mike Devenney

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-29 : 00:18:32

set dateformat dmy

DECLARE @Stock table--test table for illlustration
(
HistID int,
Cusip varchar(10),
DateLoaded datetime,
TimeLoaded datetime
)

INSERT INTO @Stock--test data
SELECT 84,'047772-CZ9','9/7/2007','16:30'
UNION ALL
SELECT 73,'047772-CZ9','9/7/2007','13:30'
UNION ALL
SELECT 65,'047772-CZ9','9/7/2007','10:30'
UNION ALL
SELECT 122,'047772-CZ9','10/7/2007','05:30'
UNION ALL
SELECT 128,'047772-CZ9','10/7/2007','11:30'
UNION ALL
SELECT 235,'047772-CZ9','11/7/2007','22:40'
UNION ALL
SELECT 284,'047772-CZ9','12/7/2007','00:30'
UNION ALL
SELECT 341,'047772-CZ9','12/7/2007','13:00'
UNION ALL
SELECT 365,'047772-CZ9','12/7/2007','19:30'


SELECT t.Cusip,t.DateLoaded,CONVERT(varchar(5),t.TimeLoaded,108) AS TimeLoaded
FROM @Stock t
INNER JOIN (SELECT Cusip,DateLoaded,MAX(TimeLoaded) as MaxTime
			FROM @Stock
			GROUP BY Cusip,DateLoaded)t1
ON t1.Cusip=t.Cusip
AND t1.DateLoaded=t.DateLoaded
AND t1.MaxTime=t.TimeLoaded
ORDER BY t.DateLoaded,t.TimeLoaded

output
---------------------------------------------------------------------
Cusip      DateLoaded              TimeLoaded
---------- ----------------------- ----------
047772-CZ9 2007-07-09 00:00:00.000 16:30
047772-CZ9 2007-07-10 00:00:00.000 11:30
047772-CZ9 2007-07-11 00:00:00.000 22:40
047772-CZ9 2007-07-12 00:00:00.000 19:30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources