CODEWITHSUNDEEP
sqlgroup-by
Sascha
Sascha

Reputation: 13

MS SQL Server: Get local min/max values from groups of values divided into time periods

suppose I have a table of float values with columns Value_ID, ValueTime, Value.

If I do this:

SELECT t.IntervalGroup
       , Min(t.Value) as MinValue  
       , Max(t.Value) as MaxValue  
       , COUNT(t.Value) as #Values  
FROM (  
       SELECT Value  
              , Valuetime  
              , (DATEDIFF(MINUTE, '2010-09-24 00:00:00', ValueTime) / 10) AS IntervalGroup  
       FROM [testdata].[dbo].T003ValueFloat  
       WHERE Tag_Id = 272 and ValueTime  between '2010-09-24' and '2010-09-26'  
     ) t  
GROUP BY t.IntervalGroup  
having t.IntervalGroup < 6  
order by t.IntervalGroup asc

I get a result like this:

IntervalGroup MinValue               MaxValue               #Values
------------- ---------------------- ---------------------- -----------
0             7,48281145095825       8,18281173706055       365
1             7,34531164169312       8,10468673706055       364
2             7,29062461853027       8,09062480926514       362
3             6,76249933242798       8,02812385559082       356
4             6,77031183242798       8,47656154632568       362
5             7,41874933242798       8,09374904632568       364

(6 row(s) affected)

So I know the number of Values per period (IntervalGroup) and the absolute min/max value of this period.

But I also need to know the Value_IDs of the corresponding min/max values.

So I'd like to get something like this:

IntervalGroup MinValue  Value_IDofMinValue  MaxValue    Value_ID_ofMaxValue #Values
------------- --------- ------------------- ----------- ------------------- -------
0             7,482811  12345               8,1828117   23456               365

Anyone out there who can help me?

TIA, Sascha

Upvotes: 1

Views: 6900

Answers (1)

Andriy M
Andriy M

Reputation: 77697

WITH datatable AS (
  SELECT
    Value,
    Valuetime,
    IntervalGroup = (DATEDIFF(MINUTE, '2010-09-24 00:00:00', ValueTime) / 10),
    Value_ID
  FROM [testdata].[dbo].T003ValueFloat
  WHERE Tag_Id = 272 and ValueTime  between '2010-09-24' and '2010-09-26'
)
SELECT
  t.IntervalGroup,
  t.MinValue,
  Value_IDofMinValue = t_min.Value_ID,
  t.MaxValue,
  Value_IDofMinValue = t_max.Value_ID,
  t.#Values
FROM (
  SELECT IntervalGroup
         , Min(Value) as MinValue
         , Max(Value) as MaxValue
         , COUNT(Value) as #Values
  FROM datatable
  WHERE IntervalGroup < 6
  GROUP BY IntervalGroup
) AS t
  INNER JOIN datatable AS t_min ON t.IntervalGroup = t_min.IntervalGroup
    AND t.MinValue = t_min.Value
  INNER JOIN datatable AS t_max ON t.IntervalGroup = t_max.IntervalGroup
    AND t.MaxValue = t_max.Value
order by t.IntervalGroup asc

Upvotes: 1

Related Questions