CODEWITHSUNDEEP
sqlselecttimestampsubquerycase
user1801843
user1801843

Reputation: 143

SQL SELECT Only Closest To 15-Minute Timestamp

I would like to do a SQL SELECT for the closest value to each 15-minute time value. For example:

00:15,
00:30,
00:45,
01:00,
01:15 etc...

based on timestamps(time) that are not quite on the 00 second stamped using the following if have managed to round every value down to the closest 15 minutes but I only want the closest one e.g.

SELECT dateadd(minute, -1 * datediff(minute, 0, 
    cast(convert(varchar(20),[time],100) as smalldatetime)) % 15, 
    dateadd(minute, datediff(minute, 0, [time]), 0)) as [TIMESTAMP],
    cast(convert(varchar(20),[time],100) as smalldatetime), [time], 
    tagname , value
FROM hdata
INNER JOIN rtdata
    ON hdata.tag_id = rtdata.id
WHERE tagname = 'M1_WH_004'
order by [TIMESTAMP] desc

(note: i need the inner join to pull the tagname as they are not in the hdata table)

produces:

example data

Therefore, for each 15 minutes, I only want the value closest to the 15-min boundary. For the data above, it would be the 09:45:15.383 and 09:30:17.463 for the data for 09:45 and 09:30 respectively.

Do I need a subquery or case statement? Any help would be greatly appreciated!

Further to this, a had a table that looked like the data in the solution (data for every 15 minutes) already and the subquery performed a calculation based on the last two values like so:

SELECT DD1.[TIME_STAMP] AS [TIME_STAMP], DD1.[kWh1] AS [kWh1], DD1.[kWh2] AS [kWh2], (DD1.[kWh1] + DD1.[kWh2]) AS [Total] FROM (SELECT a.ID
      ,a.TIME_STAMP
      ,(a.[1_M1_Wh] - (SELECT TOP 1 b.[1_M1_Wh] FROM TagCapture b WHERE b.TIME_STAMP = DATEADD(MINUTE, -15, a.TIME_STAMP))) * 0.04 AS kWh1
      ,(a.[1_M2_Wh] - (SELECT TOP 1 b.[1_M2_Wh] FROM TagCapture b WHERE b.TIME_STAMP = DATEADD(MINUTE, -15, a.TIME_STAMP))) * 0.04 AS kWh2
  FROM [TagCapture] a) DD1

How can I use the solution provided in this query? I'm a little confused with all the subqueries.

i.e. so based on the data defined by the t subquery take the count value for one 15 minute subtract from previous and multiply to get required value where would I insert the t subquery in each FROM clause? I can't seem to get it to work. The above is just where the t query would define the two different tagnames for 'M1' and 'M2'.

Thanks again in advance!!

Upvotes: 1

Views: 1460

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

It looks like you are using SQL Server. If so, then you can use row_number() to solve this.

select t.*
from (select t.*, row_number() over (partition by tagname, time15 order by time) as seqnum
      from (SELECT dateadd(minute, -1 * datediff(minute, 0, cast(convert(varchar(20),[time],100) as smalldatetime)) % 15, dateadd(minute, datediff(minute, 0, [time]), 0)) as [TIMESTAMP], cast(convert(varchar(20),[time],100) as smalldatetime) as time15,
                   [time], tagname , value
            FROM hdata INNER JOIN rtdata
                 ON hdata.tag_id = rtdata.id
            WHERE tagname = 'M1_WH_004'
          ) t
    ) t
where seqnum = 1

Upvotes: 1

Related Questions