Help with finding last record!

Hollispj

I have two columns - code (nvchar) and date (datetime) within an sql 2005 table.

The code contains a list where the items are duplicated and I need to search for each item for the newest date and if the date is older than 180 days to display that record. Was relatively easy using the Last function but this does not exist now.

Any help would be appreciated. TIA

Mugambo

PJ:

Would you please provide sample data and intended results

Dave

Duncan McC

Try something like this:

select Code, max(date) - (This will give you the most recent date)

from table_1 (- Your table)

where date <= 01/01/2006 - (make this date 180 days ago)

group by Code

Arnie Rowland

Something like this: (This should return the 'most recent' date that is at least 180 day old.)

SELECT
[Code],
max( [Date] )
FROM MyTable
WHERE [Date] < ( dateadd( day, -180, getdate() ))
GROUP BY
[Code]
[Date]

As an aside, it is a 'Best Practice' to NOT use reserved words as object names, and when they have been 'forced' on you, to
ALWAYS enclose them in square brackets [ ].

Lucky P

try this:

-- sample table / data
create table t1 ([code] nvarchar(10), [date] datetime);
insert into t1 VALUES ('AB', '2004-10-05');
insert into t1 VALUES ('AB', '2005-10-05');
insert into t1 VALUES ('CD', '2005-10-05');
insert into t1 VALUES ('CD', '2006-10-05');

-- return the code and date of doubles,
-- when the newer date is at least 180 days before today
select b.code, b.date
from t1 as a
join t1 as b
on a.code = b.code
WHERE a.date < b.date
AND b.date < (getdate() -180);

Hollispj

Thanks guys,

Will try these over the weekend when things quiten down here. No Xmas slowdown for us...

Duncan McC

Did you have any luck with the answers

Hollispj

Havent had time yet but thanks. Will post sucess when done.

Hugh Qu - MSFT

You may try this:

SELECT DISTINCT a.code, newest_date
FROM t1 AS a
CROSS APPLY (
SELECT MAX(b.date) AS newest_date
FROM t1 AS b
WHERE a.code = b.code) AS c
WHERE DATEDIFF(day, newest_date, GETDATE()) > 180

APPLY is a new table operator that is introduced in SQL Server 2005.

Hollispj

Just to let you all know, I still have not had time to try this. The xmas rush still has not slowed down

Hollispj

SELECT
[Code],
max( [Date] )
FROM MyTable
WHERE [Date] < ( dateadd( day, -180, getdate() ))
GROUP BY
[Code]
[Date]

was the first I tried and it works fine. Looking at it now, was quite simple.

Thanks all.