Group By and select most recent record 2

[scc]

First of all, I just realized that I posted the following question in the SQL Server Group, but probably should have posted it here instead. Don't mean to "over-step" boundaries, but I really need some help, so hopefully someone will come to the rescue...

I have the following SQL statement, however there may be multiple records for a TrackingNumber. The TrackingNumber and the DeliveryDate (which has a time stamp in it) make up the composite primary key. I need to look at the most recent record for each TrackingNumber and then check the criteria in my where clause, but I'm not sure how to do this.

PLEASE HELP!

SELECT
a.TrackingNumber, a.InboundDate, a.InboundUser, a.DeliveryDate, a.Recipient, a.Signature, [Enter Date as mm/dd/yyyy] AS Inbound
FROM dbo_vw_Stor_Del_2 AS a
WHERE (((a.Recipient) Is Null) AND ((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy])) OR (((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy]) AND ((UCase([Recipient])) Not In ("DRIVER RELEASE","HARD COPY SIGNATURE")));

TIA!

 

[lupins46]

To get the latest record you need this:

Select * from FROM dbo_vw_Stor_Del_2 AS a
inner join (Select trackingnumber, max(deleiverydate)
from dbo_vw_Stor_Del_2 group by trackingnumber) as q1
where a.trackingnumber = q1.trackingnumber and a.deliverydate = q1.deliverydate

 

[scc]

Thanks for responding...

I still don't quite understand how I would write the query to get all my other criteria as well.

Is this what I would do? Unfortunately, I'm not at the office at the moment to try it, but will be in a few hours and need to get this resolved.

Select * from FROM dbo_vw_Stor_Del_2 AS a
Where... (my original criteria)
inner join (Select trackingnumber, max(deliverydate)
from dbo_vw_Stor_Del_2 group by trackingnumber) as q1
where a.trackingnumber = q1.trackingnumber and a.deliverydate = q1.deliverydate

 

[PHV]

look at the most recent record for each TrackingNumber and then check the criteria
You may try this:
SELECT
a.TrackingNumber, a.InboundDate, a.InboundUser, a.DeliveryDate, a.Recipient, a.Signature, [Enter Date as mm/dd/yyyy] AS Inbound
FROM dbo_vw_Stor_Del_2 AS a INNER JOIN (
SELECT TrackingNumber, Max(DeliveryDate) As LastDelivery FROM dbo_vw_Stor_Del_2 GROUP BY TrackingNumber
) AS b WHERE a.TrackingNumber=b.TrackingNumber AND a.DeliveryDate=b.LastDelivery
WHERE a.Signature Is Null AND Format(a.InboundDate,'mm/dd/yyyy')=[Enter Date as mm/dd/yyyy] AND UCase(a.Recipient & "") Not In ('DRIVER RELEASE','HARD COPY SIGNATURE');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scc]

I'm sure it won't let me have 2 WHERE clauses on that last select.

I'll try playing with it when I get in to the office.

 

[lupins46]

Sorry - I wrote Where instead of ON

Select * from FROM dbo_vw_Stor_Del_2 AS a
inner join (Select trackingnumber, max(deleiverydate)
from dbo_vw_Stor_Del_2 group by trackingnumber) as q1
ON a.trackingnumber = q1.trackingnumber and a.deliverydate = q1.deliverydate
WHERE (((a.Recipient) Is Null) AND ((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy])) OR (((a.Signature) Is Null) AND ((Format([a.InboundDate],'mm/dd/yyyy'))=[Enter Date as mm/dd/yyyy]) AND ((UCase([Recipient])) Not In ("DRIVER RELEASE","HARD COPY SIGNATURE")));

 

[scc]

Thanks, I'll let you know when I get to this office whether this works or not...

Here's a star for you efforts!!!

 

[scc]

Thank you!!!!

I had to "tweak" a bit, correcting a misspelled word and giving the Max column a name, but after that it worked liked a charm.

I look forward to feedback from the Customer as it took the records for one day down from 269 to 27!

Thanks again...