CODEWITHSUNDEEP
sqlsql-server
robertuxo
robertuxo

Reputation: 35

Create sub query for temp column for comparison of values

I am trying to create sub query from my secondz column I want to get only 30 second or 1 minute intervals so they come back as 30 or 0. I am trying to create a subquery or another AND statement to select only rows where secondz = 0 or 30.

SELECT TOP (1000) FloatTable.[DateAndTime]
      ,DATEPART(ss, FloatTable.[DateAndTime]) AS secondz
      ,FloatTable.[TagIndex]
      ,FloatTable.[Val]
      ,TagTable.[TagName]
      ,TagTable.[TagType]
      ,TagTable.[TagDataType]
  FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
  JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
  on FloatTable.[TagIndex] = TagTable.[TagIndex]
  WHERE (TagTable.[TagName] = '[PLC]FI225'
  OR TagTable.[TagName] = '[PLC]FI250'
  OR TagTable.[TagName] = '[PLC]SS_FIT1109')
  AND TagTable.[TagIndex] = 17
  AND FloatTable.[DateAndTime] >= '04/01/2022'

Upvotes: 0

Views: 46

Answers (1)

Stu
Stu

Reputation: 32579

If you need where the seconds are only exactly 30 or 0 (60), try a modulo division:

and datepart(second, FloatTable.DateAndTime) % 30 = 0

Upvotes: 2

Related Questions