I am using an historian to search for certain values of a specific tag. The historian has certain rules, such as, I cannot create or drop tables, queries need tagnames etc.

I want to search a TagName 'Tank1' for example and return its DateTime and Value results, then search further Tags using these results to match those tags that have the same Values at that DateTime.

I search 'Tank1' between a given date and time and receive 4 results as below

So, I need to search four other Tags and return results that match the dateTime and value.

The below code is what I have produced (I should now tell you that I am a virtual novice)

DECLARE @AT1Value INT,
        @AT1DateTime DateTime
SELECT  @AT1Value = Value,                              --GETS THE VALUES OF AT1 STERILISER
        @AT1DateTime = DateTime                         --GETS THE DATETIME OF AT1 STERILISER VALUES
From Runtime.dbo.v_History
Where 
    Runtime.dbo.v_History.Tagname = 'AT1_Select_ster'
AND Runtime.dbo.v_History.DateTime >= '2021-11-02 08:00'
AND Runtime.dbo.v_History.DateTime <= '2021-11-03 08:01'
AND Runtime.dbo.v_History.Value > 0


Select  a.DateTime,
        a.TagName,
        a.Value
From Runtime.dbo.v_History AS a        --GETS  THE VALUES OF THE FM TAGS AT THE DATETIME OF AT1 STERILISER VALUES
Where  
    ((a.TagName = 'FM_S1_Batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S2_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S3_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S4_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime))
AND a.Value > 0

This works fine, albeit it only produces the last dateTime and Value result below,

Am I right in assuming this is because the Variable is being overwritten each time and only holding the last values?

The results that should be returned should look something like the results below

Is there anyway I can do several scans and save each result until I have all the results needed? or is there an easier more suitable method (which I am guessing there is).

TIA

CodePudding user response：

If I'm understanding correctly, you are hoping your int and datetime variables (@AT1DateTime and @AT1Value) will hold more than one value returned by the first query. That won't work (as you indicated, they will hold only one value).

From the code provided, it's not clear that you need to store those values in a variable at all. I think you are probably looking for something like:

Select  a.DateTime,
        a.TagName,
        a.Value
From Runtime.dbo.v_History AS a        
Where  
a.TagName IN ('FM_S1_Batch', 'FM_S2_batch', 'FM_S3_batch', 'FM_S4_batch')
AND EXISTS
 (SELECT  *
 From Runtime.dbo.v_History b
 Where 
  b.Tagname = 'AT1_Select_ster'
  AND b.DateTime >= '2021-11-02 08:00'
  AND b.DateTime <= '2021-11-03 08:01'
  AND b.Value > 0
  AND b.Value = a.Value
  AND b.DateTime = a.DateTime
 )
AND a.Value > 0

This is your two queries combined together into one. The 2nd/middle WHERE condition of the outer query checks that the a.value/a.datetime combination exists in the inner query.

CodePudding user response：

Thanks to everyone who took the time to reply to my problem. I finally got it working using a cursor thanks to @EdmCoff for suggesting trying a for-loop or a cursor.

The for-loop returned the last value 4 times so I tried the cursor and it worked for me.

I have posted the code that works for me below

DECLARE @AT1Value INT,
        @AT1DateTime DateTime
        
DECLARE cursor_result CURSOR                                

FOR SELECT  Runtime.dbo.v_History.Value,                    
        Runtime.dbo.v_History.DateTime                      
From Runtime.dbo.v_History
Where 
    Runtime.dbo.v_History.Tagname = 'AT1_Select_ster'
AND Runtime.dbo.v_History.DateTime >= '2021-11-02 08:00'
AND Runtime.dbo.v_History.DateTime <= '2021-11-04 08:01'
AND Runtime.dbo.v_History.Value > 0

OPEN cursor_result                                          
FETCH NEXT FROM cursor_result INTO                          
    @AT1Value,
    @AT1DateTime

WHILE @@FETCH_STATUS = 0                                    
Select  a.DateTime,
        a.TagName,
        a.Value
From Runtime.dbo.v_History AS a                             
Where  
    ((a.TagName = 'FM_S1_Batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S2_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S3_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime)
OR  (a.Tagname = 'FM_S4_batch' AND a.Value = @AT1Value AND a.DateTime = @AT1DateTime))
AND a.Value > 0
FETCH NEXT FROM cursor_result INTO                                      
@AT1Value,
@AT1DateTime
END
CLOSE cursor_result                                         
DEALLOCATE cursor_result