sqlsql-servervbscriptwincc

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


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!


Solution

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