CODEWITHSUNDEEP
sqlsql-server
Marcus
Marcus

Reputation: 13

SQL: Filtering the most recent date entry from a Select statement

I am hoping someone can help with this query. The MIN() function isn't working as expected, possibly because I need to have a JOIN in there somewhere but none of the joins I have tried work. Any examples I have found such as SQL: Select most recent date for each category are only basic and don't include multiple rows with matching entries on some columns or are selecting from multiple tables. 

SELECT TruckNo, MIN(ArrivalDate) AS ArrivalDate, DockId, FreightCode
FROM tblTruckArrival  
WHERE ArrivalDate <> '' AND DockId IN (660,517,86,9,253,685)  
GROUP BY TruckNo, DockId, FreightCode  
ORDER BY TruckNo

This returns something like these results which includes multiple ArrivalDates for each DockId when all I want is the next arrival date for each DockId and not future dates. If I remove the FreightCode from the Select statement I get the correct results (presumably because some entries have a different FreightCode for the same DockId) but I need to know what the FreightCode is for each row returned. 

TruckNo         ArrivalDate     DockId  FreightCode  
22              2018-07-13      253     03WS18  
22              2018-08-29      253     04WS18  
22              2018-10-16      253     05WS18  
22              2018-12-03      253     06WS27  
23              2018-09-02       86     808ES  
23              2018-09-12      685     808ES  
24              2018-11-24       86     810ES  
25              2018-06-17       86     805ES  
25              2018-06-28      517     805ES  
25              2018-12-04      517     810ES  
26              2018-07-03        9     805ES  
27              2018-07-13       86     806ES

Should return this:

TruckNo         ArrivalDate     DockId  FreightCode  
22              2018-07-13      253     03WS18  
23              2018-09-02       86     808ES  
23              2018-09-12      685     808ES  
24              2018-11-24       86     810ES  
25              2018-06-17       86     805ES  
25              2018-06-28      517     805ES  
26              2018-07-03        9     805ES  
27              2018-07-13       86     806ES

Upvotes: 0

Views: 100

Answers (1)

G.Arima
G.Arima

Reputation: 1171

If you add FreightCode in group by statement, obviously it will give results basis every FreightCode. Correct me if I am wrong, you want the FreightCode corresponding to the each truckno and min arrival date. For that try this out: 

select a.*,b.freightcode  
from
(select truckno, dockid, min(arrivaldate) as arrivaldate
from tbltruckarrival
where arrivaldate <> '' and dockid in (660,517,86,9,253,685)
group by truckno, dockid) a
left join
tbltruckarrival b
on a.truckno = b.truckno and a.arrivaldate = b.arrivaldate and a.docket_id = b.docket_id
order by a.truckno

Let me know in case of any clarifications.

Upvotes: 1

Related Questions