Help with SQL

[Jeff]

Hello,

I need help to write a Query to do the following... I am stumped.

I have a Table called tblStatuses where I want to only pull 1 record per
Proposal Number that has a Status of "New" and is the latest entry meaning
has the latest StatusDate & StatusTime

So for example if this was the data in the tblStatuses

StatusID ProposalNumber Status StatusDate StatusTime
1 07-37 White 07/24/07 11:07 AM
2 07-37 New 07/27/07 1:50 AM
6 07-37 Rejected 07/27/07 2:00 AM
7 07-37 Closed 07/27/07 2:00 AM
8 07-37 New 07/27/07 2:01 AM
3 07-38 New 07/27/07 1:51 AM
4 07-38 White 07/27/07 1:57 AM
5 07-38 New 07/27/07 1:57 AM

After the Query was run it would give the following results:
8 07-37 New 07/27/07 2:01 AM
5 07-38 New 07/27/07 1:57 AM

It gives 1 record for each ProposalNumber, that has a Status of "New" and
has the latest Date & Time

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Andy Hull]

Hi Jeff

If you only need to see the ProposalNumber and date and time then the usual
summary query will do...

SELECT tblStatuses.ProposalNumber,
max(tblStatuses.StatusDate + tblStatuses.StatusTime)
FROM tblStatuses
WHERE tblStatuses.Status = "New"
GROUP BY tblStatuses.ProposalNumber


If you need an updatable query or want to show other columns that aren't par
tof the grouping you can use something like...

SELECT * FROM tblStatuses a
WHERE a.Status = "New"
and exists
(select 0
from tblStatuses b
where a.ProposalNumber = b.ProposalNumber
and b.Status = "New"
group by b.ProposalNumber
having a.StatusDate + a.StatusTime = max(b.StatusDate + b.StatusTime)
)


You don't have to use select *, you can just select the columns you're
interested in.

Note also, that I've assumed your date and time columns are both date/time
data types and can simply be added together. If not, you may need to do some
conversion before adding them. Post back if you need to.

hth

Andy Hull

 

[Jeff]

Hi Andy,

Thank you very much for your help !

I like that Max(tblStatuses.StatusDate + tblStatuses.StatusTime) I didnt
know you could do something like that. That is what was causing me the
biggest problem trying to get the Max Date AND Time.

Thank you,
Jeff

 

[John W. Vinson]

That is what was causing me the
biggest problem trying to get the Max Date AND Time.

And that's why it's usually best to use a single date/time field for both the
date and the time, rather than two separate fields.

John W. Vinson [MVP]