How can I filter out values from an SQL database query-CodePudding

I'm trying to do some basic filtering against a historian database but not sure how it can be accomplished. I have the SQL code below (irrelevant parts removed) and would like to filter it so that it only shows data for when 'FV215_57_STAT' is equal to 3.

SET NOCOUNT ON 
DECLARE @StartDate DateTime 
DECLARE @EndDate DateTime 
SET @StartDate = '20211121 09:27:01.000' 
SET @EndDate = '20211221 09:27:01.000' 
SET NOCOUNT OFF 
SELECT  * FROM ( 
SELECT History.TagName, DateTime, Value, StartDateTime 
 FROM History 
 WHERE History.TagName IN ('FV215_57_STAT','FV215_58_STAT','FV317_28_STAT','FIC341_107_PV') 
 AND wwRetrievalMode = 'Cyclic' 
 AND wwResolution = 1800000
 AND wwVersion = 'Latest' 
 AND DateTime >= @StartDate 
 AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate

The code retrieves a tag name and a value/datetime every 30mins over 1 month. I tried

WHERE History.TagName = ('FV215_57_STAT')
 AND Value=3

This shows only the first string when its value is 3. I need the three tags after 'FV215_57_STAT' to also be filtered so that they're only displayed for times when 'FV215_57_STAT' equals 3. Is this possible?

Edit: Snippet of the raw data in a csv shown below. With the modified code, the first 4 values should be removed (since FV215_57_STAT is 1), next 4 values Would show sinceFV215_57_STAT is 3, etc..

FV215_57_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FV215_58_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FV317_28_STAT,21/11/2021 09:57:01,1,21/11/2021 09:27:01
FIC341_107_PV,21/11/2021 09:57:01,1092.48620605469,21/11/2021 09:27:01
FV215_57_STAT,21/11/2021 10:27:01,3,21/11/2021 09:57:01
FV215_58_STAT,21/11/2021 10:27:01,1,21/11/2021 09:57:01
FV317_28_STAT,21/11/2021 10:27:01,1,21/11/2021 09:57:01
FIC341_107_PV,21/11/2021 10:27:01,1090.34826660156,21/11/2021 09:57:01
FV215_57_STAT,21/11/2021 10:57:01,3,21/11/2021 10:27:01
FV215_58_STAT,21/11/2021 10:57:01,1,21/11/2021 10:27:01
FV317_28_STAT,21/11/2021 10:57:01,3,21/11/2021 10:27:01
FIC341_107_PV,21/11/2021 10:57:01,1090.25109863281,21/11/2021 10:27:01
FV215_57_STAT,21/11/2021 11:27:01,3,21/11/2021 10:57:01
FV215_58_STAT,21/11/2021 11:27:01,1,21/11/2021 10:57:01
FV317_28_STAT,21/11/2021 11:27:01,3,21/11/2021 10:57:01
FIC341_107_PV,21/11/2021 11:27:01,1094.44519042969,21/11/2021 10:57:01
FV215_57_STAT,21/11/2021 11:57:01,3,21/11/2021 11:27:01
FV215_58_STAT,21/11/2021 11:57:01,1,21/11/2021 11:27:01
FV317_28_STAT,21/11/2021 11:57:01,3,21/11/2021 11:27:01
FIC341_107_PV,21/11/2021 11:57:01,1100.19775390625,21/11/2021 11:27:01
FV215_57_STAT,21/11/2021 12:27:01,3,21/11/2021 11:57:01
FV215_58_STAT,21/11/2021 12:27:01,1,21/11/2021 11:57:01
FV317_28_STAT,21/11/2021 12:27:01,3,21/11/2021 11:57:01
FIC341_107_PV,21/11/2021 12:27:01,1103.96740722656,21/11/2021 11:57:01
FV215_57_STAT,21/11/2021 12:57:01,3,21/11/2021 12:27:01
FV215_58_STAT,21/11/2021 12:57:01,1,21/11/2021 12:27:01
FV317_28_STAT,21/11/2021 12:57:01,3,21/11/2021 12:27:01
FIC341_107_PV,21/11/2021 12:57:01,1086.94787597656,21/11/2021 12:27:01
FV215_57_STAT,21/11/2021 13:27:01,3,21/11/2021 12:57:01
FV215_58_STAT,21/11/2021 13:27:01,1,21/11/2021 12:57:01
FV317_28_STAT,21/11/2021 13:27:01,3,21/11/2021 12:57:01
FIC341_107_PV,21/11/2021 13:27:01,1089.37548828125,21/11/2021 12:57:01
FV215_57_STAT,21/11/2021 13:57:01,3,21/11/2021 13:27:01
FV215_58_STAT,21/11/2021 13:57:01,1,21/11/2021 13:27:01
FV317_28_STAT,21/11/2021 13:57:01,3,21/11/2021 13:27:01
FIC341_107_PV,21/11/2021 13:57:01,1079.55786132813,21/11/2021 13:27:01
FV215_57_STAT,21/11/2021 14:27:01,3,21/11/2021 13:57:01
FV215_58_STAT,21/11/2021 14:27:01,1,21/11/2021 13:57:01
FV317_28_STAT,21/11/2021 14:27:01,3,21/11/2021 13:57:01
FIC341_107_PV,21/11/2021 14:27:01,1082.06420898438,21/11/2021 13:57:01
FV215_57_STAT,21/11/2021 14:57:01,3,21/11/2021 14:27:01
FV215_58_STAT,21/11/2021 14:57:01,1,21/11/2021 14:27:01
FV317_28_STAT,21/11/2021 14:57:01,3,21/11/2021 14:27:01
FIC341_107_PV,21/11/2021 14:57:01,1088.82287597656,21/11/2021 14:27:01
FV215_57_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FV215_58_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FV317_28_STAT,21/11/2021 15:27:01,1,21/11/2021 14:57:01
FIC341_107_PV,21/11/2021 15:27:01,1172.87451171875,21/11/2021 14:57:01```

CodePudding user response：

I note that the data comes in groups of 4 (as implied by your requirements) and that all the rows have the same date and time, so something like this might work. I've included my demo sample data as I still can't copy it from your question (hint: post data as text not images)

declare @demo table (TagName varchar(30), [Datetime] datetime, [Value] int, StartDateTime datetime)
insert into @demo (TagName, [Datetime], [Value]) values
('FV215_57_STAT', '2022-03-01 10:00:00', 1),('FV215_58_STAT', '2022-03-01 10:00:00', 1),('FV215_28_STAT', '2022-03-01 10:00:00', 1),
('FV215_29_STAT', '2022-03-01 10:00:00', 1),('FV215_57_STAT', '2022-04-01 10:00:00', 3),
('FV215_58_STAT', '2022-04-01 10:00:00', 3),('FV215_28_STAT', '2022-04-01 10:00:00', 3),
('FV215_29_STAT', '2022-04-01 10:00:00', 3),('FV215_57_STAT', '2022-05-01 10:00:00', 2),
('FV215_58_STAT', '2022-05-01 10:00:00', 2),('FV215_28_STAT', '2022-05-01 10:00:00', 2),
('FV215_29_STAT', '2022-05-01 10:00:00', 2),('FV215_57_STAT', '2022-06-01 10:00:00', 3),
('FV215_58_STAT', '2022-06-01 10:00:00', 3),('FV215_28_STAT', '2022-06-01 10:00:00', 3),
('FV215_29_STAT', '2022-06-01 10:00:00', 3);

;with cte as
(
    select [Datetime] as dt
    from @demo
    where [Value] = 3 and TagName = 'FV215_57_STAT'
)
select * 
from cte
join @demo on dt=[Datetime];

I've used a "Common Table Expression" to get my initial dates - see https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

That might not be a safe assumption so here is an alternative

;with cte as
(
    -- Give every row a number
    select ROW_NUMBER() OVER (ORDER BY [DateTime]) as rn
    ,TagName, [Datetime],[Value]
    from @demo
), cte2 as
(
    -- get the row number of the items we want
    select cte.rn, cte.TagName, cte.Datetime, cte.Value
    from cte
    where [Value] = 3 and TagName = 'FV215_57_STAT'
)
select d.* 
from cte2 c
-- go back to the original cte to get the row number from cte2 and the following 3 rows
inner join cte d on c.rn = d.rn or c.rn   1 = d.rn or c.rn   2 = d.rn or c.rn   3 = d.rn
order by d.[Datetime]