Correcting my SQL query query

Question

I have a query that I am trying to write that displays the MOST RECENT StatusID data from an action table.

Here's what my database looks like (Screenshot from SQL Server 2008):

From my sample data, you can see that the action table contains two (2) entries for RequestID # 26. I want to show only the last StatusID value (based on the DateStamp field).

I created a view for my database. It looks unpleasant, and stumbles upon my possibilities of writing SQL.

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN Action AS A ON A.RequestID = R.ID INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID 

ALL values ​​are displayed in this view, and I need it to somehow pull out the very last line for any given action.

How do I change my view to do this?

Answer 1

You can create a view from the action table that captures the maximum DateStamp grouped by RequestID (this will give you the last DateStamp for each RequestID). After you receive the view, you can join it in the action table in a row from the action table that has the corresponding last DateStamp for this RequestID.

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN (SELECT RequestID, MAX(DateStamp) AS MostRecentDateStamp FROM Action GROUP BY RequestID) AS MostRecentAction ON R.ID = MostRecentAction.RequestID INNER JOIN Action AS A ON MostRecentAction.RequestID = A.RequestID AND MostRecentAction.MostRecentDateStamp = A.DateStamp INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID 

or, another option is to take the approach shown by Mr. Carwin here: Enter one row from a table in MySQL

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN Action AS A ON R.ID = A.RequestID LEFT JOIN Action AS A2 ON A.RequestID = A2.RequestID AND A.DateStamp < A2.DateStamp INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID WHERE A2.RequestID IS NULL 

I like the approach used by Mr. Carwin, especially when dealing with structure relationships like yours:

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN Action AS A ON R.ID = A.RequestID LEFT JOIN Action AS A2 ON A.RequestID = A2.RequestID AND (A.DateStamp < A2.DateStamp OR (A.DateStamp = A2.DateStamp AND A1.RequestID < A2.RequestID)) INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID WHERE A2.RequestID IS NULL 

Answer 2

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN Action AS A ON A.RequestID = R.ID INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID where A.StatusID = ( select top 1 StatusID from Action where RequestID = R.ID order by DateStamp desc ) 

Answer 3

To achieve what you want, you can join the subqueries by grouping the results using the package identifier and selecting MAX (ID) for each package identifier. This works because the ID field is the identity column, and therefore the highest number will always be the most recent. This is preferable to comparing with a timestamp, because ints (especially indexed ints) are much faster compared to timestamps.

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN (SELECT MAX(ID) as ID FROM Request GROUP BY PacketID) as UR ON P.ID = UR.ID INNER JOIN Request AS R ON R.PacketID = UR.ID INNER JOIN Action AS A ON A.RequestID = R.ID INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID 

Answer 4

This will work.

 WITH MaxDate AS ( SELECT RequestID, Max(DateStamp) AS MaxDate FROM Action GROUP BY RequestID ), ActionFiltered AS ( SELECT Action.* FROM Action JOIN MaxDate ON Action.RequestID=MaxDate.RequestID AND Action.DateStamp = MaxDate.MaxDate ) SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM ActionFiltered A JOIN Request AS R ON A.RequestID = R.ID JOIN Packet AS P ON P.ID = R.PacketID JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID JOIN Line AS L ON R.LineID = L.ID JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID JOIN Status AS S ON A.StatusID = S.ID 

Here's what I do: first, for each ID request, I find the most recent date (MaxDate), then I get all the data from the action table for these rows (ActionFiltered), finally, I join all this back your tables with internal joins .

Potential problem: if there are two records in the action table with the same request identifier and timestamp, you will get two rows in the final table.

Note. I have not tested, so there may be typos.

Answer 5

I usually use rank () to get the latest version of a time-based entry. It assigns the rank of each version of the record based on the key that you provide (section: in this case, the request identifier). If you order in descending order, rows with a rank of 1 are the newest. If you order asc, rows with a rank of 1 are the oldest.

EDIT: Changed the name of the RequestId column that was returned in the subquery to remove the error you saw.

 SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN ( select req.ID as RequestIdForJoin , act.* , rank() over (partition by req.ID order by act.DateStamp desc) as [Rank] from Request as req inner join Action as act on req.ID = act.RequestID ) as A on R.ID = A.RequestIdForJoin INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID where A.[Rank] = 1 

For repetitive actions: If the @Hogan script for multiple actions with the same timestamp is possible, you can execute and remove duplicates as follows:

 declare @View table ( PacketID int, RequestID int, ActionID int, EmpID int, DateStamp datetime, RequestType int, Line int, PartNo varchar(50), Workorder int, Qty int, ReasonType int, MTF varchar(50), Status int ) insert into @View SELECT P.ID AS PacketID, R.ID AS RequestID, A.ID AS ActionID, A.EmpID, P.DateStamp, RQ.Description AS RequestType, L.Description AS Line, R.PartNo, R.Workorder, R.Qty, RZ.Description AS ReasonType, R.MTF, S.Description AS Status FROM Packet AS P LEFT OUTER JOIN Request AS R ON R.PacketID = P.ID INNER JOIN ( select req.ID as RequestIdForJoin , act.* , rank() over (partition by req.ID order by act.DateStamp desc) as [Rank] from Request as req inner join Action as act on req.ID = act.RequestID ) as A on R.ID = A.RequestIdForJoin INNER JOIN RequestType AS RQ ON R.RequestTypeID = RQ.ID INNER JOIN Line AS L ON R.LineID = L.ID INNER JOIN ReasonType AS RZ ON R.ReasonTypeID = RZ.ID INNER JOIN Status AS S ON A.StatusID = S.ID where A.[Rank] = 1 -- Removing all but one duplicate ;with dups as ( select RequestID ,row_number() over (partition by RequestID order by DateStamp) as [RowNumber] from @View ) delete dups where [RowNumber] > 1 select * from @View