1

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!

Dale K
  • 25,246
  • 15
  • 42
  • 71
TCoopSC
  • 13
  • 4
  • 1
    Please edit your question and add in your query, and explain what part of the query isn't giving you the results you expect – devlin carnate Jul 14 '21 at 17:57
  • Also, please add a tag on your question for the specific type of sql you're working with (oracle, ms-sql, mysql, etc) – devlin carnate Jul 14 '21 at 17:57
  • As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. – Dale K Jul 14 '21 at 20:42

2 Answers2

0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Wonderland Alice
  • 55
  • 1
  • 8
  • FYI `case` is an *expression* not a *statement*. – Dale K Jul 14 '21 at 20:44
  • So below is how i modified your code above and it seems to be working great! Thank you very much!! – TCoopSC Jul 15 '21 at 12:42
  • 1
    SELECT z.* FROM (SELECT RANK() OVER(PARTITION BY [2DMatrix] ORDER BY [Date_Time] DESC) AS rankval, [2DMatrix], [PartStatusSt1], [Date_Time] FROM [AppsData].[dbo].[OilPumpCoverTest]) AS z WHERE z.rankval = 1 and [2DMatrix] like 'Test' – TCoopSC Jul 15 '21 at 12:42
  • Very cool, looks great. Glad I could help! – Wonderland Alice Jul 16 '21 at 22:31
0
"select top 1 * 
from [AppsData].[dbo].[OilPumpCoverTest] 
where [2DMatrix] like '" & HMIRuntime.Tags("2DMatrix").Read(1) & "' order by Date_Time desc "
h4801681gmailcom
  • 16
  • 2