Need to lookup part data when there are multiple rows of the same part

Question

I have a SQL Table that has parts with their own 2D barcodes. There may be multiple rows of the same part. Each row has a column with the Date_Time stamp.

What I need is to get the latest part data and view one of the columns to see if that value is INT 1.

Example below: Look up the latest timestamp for part "5" and see if "PartStatusSt1" is '1'

enter image description here

enter image description here

Here is my query:

"select * from [AppsData].[dbo].[OilPumpCoverTest] where [2DMatrix] like '" & HMIRuntime.Tags("2DMatrix").Read(1) & "'"

Then I need to look at column name "PartStatusSt1" and move that INT value to a WinCC Tag as described below:

HMIRuntime.Tags("Sql_Station1_Status").Write
Recordset.Fields("PartStatusSt1").Value,1

The code above works but it grabs a random row of part data if I have multiple rows of the same part (via 2DMatrix). I need to grab the latest data per Date_Time stamp.

NOTE: My code is in WinCC via VBS.

Thanks for any help!

Answer 1

"select top 1 * 
from [AppsData].[dbo].[OilPumpCoverTest] 
where [2DMatrix] like '" & HMIRuntime.Tags("2DMatrix").Read(1) & "' order by Date_Time desc "

Answer 2

To get the latest part records, I'd start by using RANK to assign a number to each part based on its time stamp. For example, if part A has three time stamps, each record would be assigned rank 1, 2, and 3, depending on your sorting. I do something similar for the part numbers I work with. To get the latest records for parts, you can then query all where rankval = 1.
Small sample below...

SELECT  z.* 
FROM
(SELECT RANK() OVER(PARTITION BY PartNo, LotNumber ORDER BY DatePosted DESC) AS rankval                                                            , TagNo, PartNo, LotNumber, DatePosted FROM PartTable) AS z
--WHERE z.rankval = 1

You could then use a case statement or where clause to check for the part or part status.