0

I have a live data stream on a 2 minutes interval, that I wish to analyze in (near) real time. A snippet of this data is shown here:

DataSnippet at 2 Mins Interval

This data is getting stored in a SQL Server table.

Now, I am trying to code a stored procedure in SQL Server 2008 that fulfills this condition:

Highest - Max(Start,End) > Absolute Value(Start - End),

ITEM3 fulfills this condition.

Additionally, for ALL the items which fulfill the above condition, it should then go back in time and return the start of the first record which fulfills the condition of End (Value) > Start (Value)

So, in the case above, the value returned should be 209.1 & Item 3.

The condition is getting fulfilled for ITEM3 only in the above case @10:21 A.M.

Additional note: for the purpose of this query, the values of lowest are not being used.

Also, there are no Zero / Null values in this data stream (the .. is temporarily there).

With my limited knowledge on SQL subqueries etc, I am unable to get the desired result beyond the first condition.

select desc 
from table1                     
where Highest - dbo.InlineMax(Start,End) > abs(Start- End)

InlineMax is my UDF which returns the higher value.

TIA

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Suraj_j
  • 151
  • 2
  • 14
  • How is the data fed into the table? What kind of history do you want to analyse over? 5 minutes? 5 days? Will the output only be two scalars: Item and a calc'd number? I can't understand your logic from your example - can you put a more detailed example? – Nick.Mc Nov 03 '18 at 01:21
  • 1
    You are going to have to first tally all of your additional conditions( End (Value)> Start (Value) ) and plug a derived row_number by desc onto the result set so you can later reference that with a MAX(row_number) LEFT JOIN WHERE X<=WhatIAmLookingFor into a lower CTE that calculates condition 1, which will also call for a MAX() derived index comparison LEFT JOIN WhatYouAreLokingFor into another CTE...I could do this but it would take me a half day. – Ross Bush Nov 03 '18 at 01:33
  • @ Nick, The Data is fed into the table my a mechanism which i don't have access to. The Output will also include the Time (sorry i missed that), I will Try to include a more detailed example with at Least 2-3 instances, – Suraj_j Nov 03 '18 at 02:07
  • Thank You Ross, for pointing me in the Right direction, Now I know how i can approach this. – Suraj_j Nov 03 '18 at 02:10

1 Answers1

1

This should get you what you want.

SELECT 
    [desc], [highest], [start], [end]
FROM 
    items
GROUP BY 
    [desc], [highest], [start], [end]
HAVING
    (MAX([start]) > MAX([end]) and highest - MAX([start]) > ABS([start]-[end]))
    OR
    (MAX([start]) <= MAX([end]) and highest - MAX([end]) > ABS([start]-[end]))

group by the relevant columns and then filter it out using the having clause.

  • If max(start) > max(end) then require highest - max(start) > abs(start-end)
  • If max(start) <= max(end) then require highest - max(end) > abs(start-end)
h33
  • 1,104
  • 3
  • 16
  • 29
  • Thanks Hugh for this, However the Output I am getting is just a Lot of Rows with Desc, Highest, Start N End, Not all of these satisfy my Conditions. The Expected output is 209.1, Item 3, 10:21 . - Suraj – Suraj_j Nov 03 '18 at 07:20