SQL Show Only the Most Recent DateTime Values

Question

My sql string is off just a little bit and I need help to get it exactly right. I have tried a number of strings with no luck. I've tried most of the examples in this thread, with no luck. Concerning the sql, here is a screenshot of what it looks like with just the results:

enter image description here

I want it to only display the value for the most recent DateTime for PRecipeRunning and ERecipeRunning.

I have setup a SQLFiddle here, with the schema and the grab everything code.

[Edit, split my two parter into two separate questions. Here is the sql question and I will do another for the visual studio part of it.]

Probably because I don't know any better. When I did a recent Excel VBA script it's what I used, so I was familiar with it. I am open to connecting to sql a different way. — Ty., Mar 26 '15 at 13:43
You have 2 separate questions here, so you need to ask each one in a separate post. The people who are SQL experts are not necessarily the same people that are Winforms experts. You will get better answers by separating the questions. Also, leaving it like this could possibly lead to the question being closed. — Becuzz, Mar 26 '15 at 13:44

score 1 · Accepted Answer · answered Mar 26 '15 at 14:24

1

Here is a query to find the most recent date-time values by TagName

SELECT A.[DateTime]
 ,A.[TagName]
 ,A.[Value]
FROM [v_StringHistory] A 
INNER JOIN 
    (SELECT MAX(B.[DateTime]) [MaxDate]
         ,B.[TagName]
    FROM [v_StringHistory] B
    GROUP BY B.[TagName]) MD
      ON MD.[TagName] = A.[TagName] 
           AND A.[DateTime] = MD.MaxDate
WHERE A.[TagName] = 'ERecipeRunning' 
    OR A.[TagName] = 'PRecipeRunning'

answered Mar 26 '15 at 14:24

Steven

896
2
16
29

This works, thanks! It's more complicated than I anticipated. I updated the sql fiddle: http://sqlfiddle.com/#!6/d6332/11/0 – Ty. Mar 26 '15 at 15:23
When I use in my actual database, I get an error message. But it works in sql. I think the error message is related to the type of system that is populating it. `OLE DB provider "INSQL" for linked server "INSQL" returned message "History queries must contain at least one valid tagname". Msg 7320, Level 16, State 2, Line 48 Cannot execute the query "SELECT Tbl1004.DateTime Col1010,Tbl1004.TagName Col1011 FROM Runtime.dbo.StringHistory Tbl1004 WHERE Tbl1004.TagName>=N'ERecipeRunning' AND Tbl1004.TagName<=N'PRecipeRunning'" against OLE DB provider "INSQL" for linked server "INSQL". ` – Ty. Mar 26 '15 at 15:37
That was supposed to say it works in sql fiddle, but not my actual database because of some linked table that the vendor is using as part of the front end. So it may not be possible to do within SQL. – Ty. Mar 26 '15 at 16:28

score 1 · Answer 2 · answered Mar 27 '15 at 08:06

1

Here's a simple version I use all the time. Check out the SQL Fiddle

SELECT  sh.[DateTime],
        sh.[TagName],
        sh.[Value]
  FROM  [v_StringHistory] sh
WHERE   sh.[TagName] in( 'ERecipeRunning', 'PRecipeRunning' )
  and   sh.[DateTime] =(
        select  Max( [DateTime] )
        from    [v_StringHistory]
        where   [TagName] = sh.[TagName] );

answered Mar 27 '15 at 08:06

TommCatt

5,498
1
13
20

The SQL Fiddle example is working great and I believe that I had tried this earlier, but no dice in the actual databaase. The front end is some industrial software called Wonderware and apparently it is using some linked tables to generate the view that I am polling against and they do not play well with some portion of these scripts. Google has found other people in the same boat, but no fixes. Same error message as above: "OLE DB provider "INSQL" for linked server "INSQL" returned message "History queries must contain at least one valid tagname". – Ty. Mar 27 '15 at 20:33

score 1 · Answer 3 · answered Mar 27 '15 at 21:27

For this particular front-end Wonderware, there are apparently some rules about how you query their tables. Luckily they have a GUI that allows you to pick and click what you want to see and then it spits out the SQL code. In this case, the code ended up being:

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DateAdd(mi,-5,GetDate())
SET @EndDate = GetDate()
SET NOCOUNT OFF
SELECT  * FROM (
SELECT History.TagName, DateTime = convert(nvarchar, DateTime, 21), Value, vValue, StartDateTime
 FROM History
 WHERE History.TagName IN ('ERecipeRunning', 'PRecipeRunning')
 AND wwRetrievalMode = 'Cyclic'
 AND wwCycleCount = 2
 AND wwVersion = 'Latest'
 AND DateTime >= @StartDate
 AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate
ORDER BY DateTime DESC

I had forgotten about the Query application and the SQL code that it provides. In my hunting and guessing, I hadn't even chosen the same table / view. But in the end, this is working and I am good. Thanks to everyone for their suggestions.

score 0 · Answer 4 · answered Mar 26 '15 at 14:01

0

To select the entry with the most recent datetime-value, order your results by that column and select the top 1: SELECT TOP 1 <columns> FROM <table> WHERE <condition> ORDER BY [DateTime] DESC

I can't help with the second question but recommend using ADO.NET.

answered Mar 26 '15 at 14:01

Patrik

1,355
12
22

What are the benefits of using ADO.NET? Would this work for showing both Values for each tag, or just the most recent single tag? – Ty. Mar 26 '15 at 15:23

SQL Show Only the Most Recent DateTime Values

4 Answers4