Last two dates

  • Thread starter Thread starter PK
  • Start date Start date
P

PK

I have a table with Project number, Report Date

How would i select the records for the last two report dates for each project?
 
Try this --
SELECT Q.[Project number], Q.[Report Date], (SELECT COUNT(*) FROM
[YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1)<=2))
ORDER BY Q.[Project number], Q.[Report Date] DESC;
 
Thanks for the reply Karl - i tried this but only got one date for each ID

KARL DEWEY said:
Try this --
SELECT Q.[Project number], Q.[Report Date], (SELECT COUNT(*) FROM
[YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1)<=2))
ORDER BY Q.[Project number], Q.[Report Date] DESC;

--
KARL DEWEY
Build a little - Test a little


PK said:
I have a table with Project number, Report Date

How would i select the records for the last two report dates for each project?
Click to expand...
Click to expand...
 
Do you have any records the field contains null? Set criteria on date field
'Is Not Null.'

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


PK said:
Thanks for the reply Karl - i tried this but only got one date for each ID

KARL DEWEY said:
Try this --
SELECT Q.[Project number], Q.[Report Date], (SELECT COUNT(*) FROM
[YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1 AS Rank
FROM YourTable AS Q
WHERE ((((SELECT COUNT(*) FROM [YourTable] Q1
WHERE Q1.[Project number] = Q.[Project number]
AND Q1.[Report Date] >= Q.[Report Date])+1)<=2))
ORDER BY Q.[Project number], Q.[Report Date] DESC;

--
KARL DEWEY
Build a little - Test a little


PK said:
I have a table with Project number, Report Date

How would i select the records for the last two report dates for each project?
Click to expand...
Click to expand...
Click to expand...
 
If your field names or table names have spaces, you will need to surround each
with square brackets for the following to work.

SELECT ProjectNumber, ReportDate
FROM SomeTable
WHERE ReportDate in
(SELECT Top 2 ReportDate
FROM SomeTable as Temp
WHERE Temp.ProjectNumber = SomeTable.ProjectNumber
ORDER BY Temp.ReportDate Desc)

That will be slow with large numbers of records, but it will be updatable.

This will probably be faster, but you won't be able to update the records

SELECT A.ProjectNumber, A.ReportDate
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.ProjectNumber = B.ProjectNumber
AND A.ReportDate >= B.ReportDate
Group By A.ProjectNumber, A.ReportDate
HAVING Count(B.ReportDate) <= 2

Both of those may not return what you want if you have a ProjectNumber with
the same date multiple times. In those cases (depending on which query you
use) you may get back more than two records for some projects or no records
for some projects.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you John - that worked PERFECTLY!

John Spencer said:
If your field names or table names have spaces, you will need to surround each
with square brackets for the following to work.

SELECT ProjectNumber, ReportDate
FROM SomeTable
WHERE ReportDate in
(SELECT Top 2 ReportDate
FROM SomeTable as Temp
WHERE Temp.ProjectNumber = SomeTable.ProjectNumber
ORDER BY Temp.ReportDate Desc)

That will be slow with large numbers of records, but it will be updatable.

This will probably be faster, but you won't be able to update the records

SELECT A.ProjectNumber, A.ReportDate
FROM SomeTable as A INNER JOIN SomeTable as B
ON A.ProjectNumber = B.ProjectNumber
AND A.ReportDate >= B.ReportDate
Group By A.ProjectNumber, A.ReportDate
HAVING Count(B.ReportDate) <= 2

Both of those may not return what you want if you have a ProjectNumber with
the same date multiple times. In those cases (depending on which query you
use) you may get back more than two records for some projects or no records
for some projects.
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
 
Back
Top