problem retrieving correct max(date) row from this dataset

Hello,
I respectfully request if someone could help me modify the following query:
The goal is to retrieve a record with a curExpireDate of 12/31/05 where ther
e
is more than one row for a given curRecordID - note: in the following
dataset there is only one correct row that I want to retrieve. So for given
curRecordID there is more than one row for a curExpireDate >= 12/31/05. The
n
if curEntryDate for a curExpireDate of 12/31/05 is greater than the
curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then I
want to retrieve that row.
The following subdataset (the actual dataset contains thousands of rows
where I need to do the same thing) contains one row where Max(curExpireDate)
= 12/31/05. I don't want that row. There are 3 more curRecordID's with
curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
18537 this ID contains a row for curExpireDate = 12/31/05 where
Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
12/31/06 for ID = 18537. None of the otherID's has this condition. I need
to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
12/31/05.
Here is the dataset and the query I have been experimenting with:
create table #temp4(curRecordID int,
curEntryDate datetime, curExpireDate datetime)
insert Into #temp4
select 12783, '2005-04-07', '2005-12-31' Union
select 12783, '2004-01-12', '2005-12-31' Union
select 12783, '2006-02-03', '2006-12-31' Union
select 12783, '2005-01-11', '2006-12-31' Union
select 29714, '2005-06-29', '2005-12-31' Union
select 29714, '2005-02-02', '2006-12-31' Union
select 29714, '2005-12-01', '2006-12-31' Union
select 18537, '2004-12-10', '2005-12-31' Union
select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
select 18537, '2006-01-10', '2006-12-31' Union
select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
SELECT * FROM #temp4 as X
WHERE
curRecordID NOT IN --the list of recid from beginning
(SELECT curRecordID FROM #temp4 GROUP BY curRecordID
HAVING MAX(curExpireDate)='12/31/05')
AND
curExpireDate = '12/31/05' --order by curRecordID
AND curEntryDate >
(select top 1 curEntryDate from #temp4 as Y
where X.curRecordID = Y.curRecordID
and Y.curExpireDate =
(select max(curExpireDate) from #temp4 as Z
where X.curRecordID = Z.curRecordID))
This is the current resultset - which is not the desired resultset
curRecID curEntryDate curExpireDate
12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
There is no way for me to isolate my desired row from this resultset
The desired resultset is this:
curRecID curEntryDate curExpireDate
18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
Any help appreciated,
Thanks,
RichI think I found my problem. I changed
(select top 1 curEntryDate from #temp4 as Y...
to
(select Max(curEntryDate) from #temp4 as Y
This is now giving me the desired result in my test case. Hopefully, will
do the same for the actual data.
"Rich" wrote:

> Hello,
> I respectfully request if someone could help me modify the following query
:
> The goal is to retrieve a record with a curExpireDate of 12/31/05 where th
ere
> is more than one row for a given curRecordID - note: in the following
> dataset there is only one correct row that I want to retrieve. So for giv
en
> curRecordID there is more than one row for a curExpireDate >= 12/31/05. T
hen
> if curEntryDate for a curExpireDate of 12/31/05 is greater than the
> curEntryDate for curExpireDate of 12/31/06 (for the same curRecordID) then
I
> want to retrieve that row.
> The following subdataset (the actual dataset contains thousands of rows
> where I need to do the same thing) contains one row where Max(curExpireDat
e)
> = 12/31/05. I don't want that row. There are 3 more curRecordID's with
> curExpireDate of 12/31/05 and 12/31/06. For the rows with curRecordID =
> 18537 this ID contains a row for curExpireDate = 12/31/05 where
> Max(curEntryDate) is greater than the curEntryDate for curExpireDate of
> 12/31/06 for ID = 18537. None of the otherID's has this condition. I nee
d
> to retrieve ID 18537 where curEntryDate = 1/27/06 and curExpireDate =
> 12/31/05.
> Here is the dataset and the query I have been experimenting with:
> create table #temp4(curRecordID int,
> curEntryDate datetime, curExpireDate datetime)
> insert Into #temp4
> select 12783, '2005-04-07', '2005-12-31' Union
> select 12783, '2004-01-12', '2005-12-31' Union
> select 12783, '2006-02-03', '2006-12-31' Union
> select 12783, '2005-01-11', '2006-12-31' Union
> select 29714, '2005-06-29', '2005-12-31' Union
> select 29714, '2005-02-02', '2006-12-31' Union
> select 29714, '2005-12-01', '2006-12-31' Union
> select 18537, '2004-12-10', '2005-12-31' Union
> select 18537, '2006-01-27', '2005-12-31' Union --<--yes want this row
> select 18537, '2006-01-10', '2006-12-31' Union
> select 38537, '2004-12-10', '2005-12-31' --<--don't want this row
> SELECT * FROM #temp4 as X
> WHERE
> curRecordID NOT IN --the list of recid from beginning
> (SELECT curRecordID FROM #temp4 GROUP BY curRecordID
> HAVING MAX(curExpireDate)='12/31/05')
> AND
> curExpireDate = '12/31/05' --order by curRecordID
> AND curEntryDate >
> (select top 1 curEntryDate from #temp4 as Y
> where X.curRecordID = Y.curRecordID
> and Y.curExpireDate =
> (select max(curExpireDate) from #temp4 as Z
> where X.curRecordID = Z.curRecordID))
>
> This is the current resultset - which is not the desired resultset
> curRecID curEntryDate curExpireDa
te
> 12783 2005-04-07 00:00:00.000 2005-12-31 00:00:00.000 --X
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000 -- +
> 29714 2005-06-29 00:00:00.000 2005-12-31 00:00:00.000 --X
> There is no way for me to isolate my desired row from this resultset
> The desired resultset is this:
> curRecID curEntryDate curExpireDa
te
> 18537 2006-01-27 00:00:00.000 2005-12-31 00:00:00.000
> Any help appreciated,
> Thanks,
> Rich