Getting Most Recent Date for Many IDs

Hello,
Like the subject says, I'm trying to get the most recend End Date for
multiple customer IDs in a table. The table, Rate, has multiple
customer IDs and each ID may have multiple data in the endDate column.
What I am attempting to do is isolate the most recent endDate for each
ID and am having a successful time pulling my hair out while doing so!
Any suggestions would be grand. Thanks!SELECT
CustomerID,
MAX(EndDate)
FROM
Table
GROUP BY
CustomerID;
<mgabig@.gmail.com> wrote in message
news:1146755218.517964.314510@.v46g2000cwv.googlegroups.com...
> Hello,
> Like the subject says, I'm trying to get the most recend End Date for
> multiple customer IDs in a table. The table, Rate, has multiple
> customer IDs and each ID may have multiple data in the endDate column.
> What I am attempting to do is isolate the most recent endDate for each
> ID and am having a successful time pulling my hair out while doing so!
> Any suggestions would be grand. Thanks!
>|||> ...each ID may have multiple data in the endDate column.
Is your model properly normalized?
Try this:
select ID
,max(<date column> ) as MaxDatePerID
from <table>
group by ID
For a better answer post DDL, sample data end expected results.
ML
http://milambda.blogspot.com/|||I suspect from what you've described (which is a little vague) you've
got a situation like this (note I've deliberately put some duplicate
data in to highlight the issue with this, but assuming you can
guarantee the uniqueness of the date column per customer then it will
work):
CREATE TABLE #tmp(customerID int, EventID int, Eventdate datetime,
otherstuff varchar(100))
INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
values(1,1,'2005-05-06', 'some stuff')
INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
values(1,2,'2005-05-07', 'some other stuff')
INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
values(2,3,'2005-05-01', 'some more stuff')
INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
values(2,4,'2005-05-01', 'some more more stuff')
/* won't work because you can't guarantee that there is only 1 event id
SELECT CustomerID, EventID, MAX(EventDate)
FROM #tmp
GROUP BY CustomerID
*/
SELECT CustomerID, EventID, EventDate
FROM #tmp as t
WHERE t.EventDate = (SELECT MAX(EventDate) FROM #tmp as t2 where
t2.CustomerID = t.CustomerID)
DROP TABLE #tmp|||Where did the EventID come from?
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1146756171.626812.171440@.g10g2000cwb.googlegroups.com...
>I suspect from what you've described (which is a little vague) you've
> got a situation like this (note I've deliberately put some duplicate
> data in to highlight the issue with this, but assuming you can
> guarantee the uniqueness of the date column per customer then it will
> work):
> CREATE TABLE #tmp(customerID int, EventID int, Eventdate datetime,
> otherstuff varchar(100))
> INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
> values(1,1,'2005-05-06', 'some stuff')
> INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
> values(1,2,'2005-05-07', 'some other stuff')
> INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
> values(2,3,'2005-05-01', 'some more stuff')
> INSERT INTO #tmp(CustomerID, EventID, EventDate, OtherStuff)
> values(2,4,'2005-05-01', 'some more more stuff')
> /* won't work because you can't guarantee that there is only 1 event id
> SELECT CustomerID, EventID, MAX(EventDate)
> FROM #tmp
> GROUP BY CustomerID
> */
> SELECT CustomerID, EventID, EventDate
> FROM #tmp as t
> WHERE t.EventDate = (SELECT MAX(EventDate) FROM #tmp as t2 where
> t2.CustomerID = t.CustomerID)
>
> DROP TABLE #tmp
>|||Is this what you want?
Select <column liat>
from Rate R1
where endDate = (select max(R2.endDate
from Rate R2
where R2.CostomerID = R1CustomerID
)
<mgabig@.gmail.com> wrote in message
news:1146755218.517964.314510@.v46g2000cwv.googlegroups.com...
> Hello,
> Like the subject says, I'm trying to get the most recend End Date for
> multiple customer IDs in a table. The table, Rate, has multiple
> customer IDs and each ID may have multiple data in the endDate column.
> What I am attempting to do is isolate the most recent endDate for each
> ID and am having a successful time pulling my hair out while doing so!
> Any suggestions would be grand. Thanks!
>|||This code is probably the closest I've gotten to work.
Some sample data would be:
ID|Rate|endDate
101|100.00|2000-01-31
101|150.00|2002-05-15
101|200.00|2100-12-31
188|200.00|2006-01-01
188|200.00|2002-01-01
When I run the query I expect the results from this data to be
101|200.00|2100-12-31
188|200.00|2006-01-01|||And what if the data were:
101|100.00|2000-01-31
101|150.00|2002-05-15
101|200.00|2006-12-31
101|175.00|2100-12-31
101|200.00|2008-12-31
188|200.00|2006-01-01
188|200.00|2002-01-01
188|129.00|2006-04-01
When I run the query I expect the results from this data to be
?
<mgabig@.gmail.com> wrote in message
news:1146757409.479673.30160@.j73g2000cwa.googlegroups.com...
> This code is probably the closest I've gotten to work.
> Some sample data would be:
> ID|Rate|endDate
> 101|100.00|2000-01-31
> 101|150.00|2002-05-15
> 101|200.00|2100-12-31
> 188|200.00|2006-01-01
> 188|200.00|2002-01-01
> When I run the query I expect the results from this data to be
> 101|200.00|2100-12-31
> 188|200.00|2006-01-01
>|||aha - there's the eventID, you've called it "rate", apart from that the
example I posted should work:
SELECT ID, Rate, EndDate
FROM tableA as t
WHERE t.EndDate= (SELECT MAX(EndDate) FROM TableA as t2 where
t2.ID= t.ID)
I didn't have a decent ddl, so I had to make up what you'd called your
columns, but I had a feeling there'd be a third one in there somewhere.
Cheers
Will|||Nice thinking... Will, I think you can save this script somewhere. and use i
t
next time. Or did you use the old script this time ? There should be an
article in the aspfaq for this.. right?
--
"Will" wrote:

> aha - there's the eventID, you've called it "rate", apart from that the
> example I posted should work:
> SELECT ID, Rate, EndDate
> FROM tableA as t
> WHERE t.EndDate= (SELECT MAX(EndDate) FROM TableA as t2 where
> t2.ID= t.ID)
> I didn't have a decent ddl, so I had to make up what you'd called your
> columns, but I had a feeling there'd be a third one in there somewhere.
> Cheers
> Will
>