Help with SQL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
That is what was causing me the
biggest problem trying to get the Max Date AND Time.
Click to expand...

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]
 
Back
Top