Sql-server – Find the first datetime interval

sql serversql-server-2005

I have a table in SQL with a column Recordtime and valueasreal

RecordTime           ValueAsReal
2015-11-30 12:59:00  195,6907
2015-11-30 13:00:00  203,8667
2015-11-30 13:01:00  198,0907
2015-11-30 13:02:00  198,3573
2015-11-30 13:03:00  208,064
2015-11-30 13:04:00  211,3066
2015-11-30 13:05:00  219,36
2015-11-30 13:06:00  233,4453
2015-11-30 13:07:00  232,8106
2015-11-30 13:08:00  217,872
2015-11-30 13:09:00  214,464
2015-11-30 13:10:00  200,0587
2015-11-30 13:11:00  173,776
2015-11-30 13:12:00  181,1787

I need to find the first 5 minute interval where the valueasreal >= 200, in this case the query would return

2015-11-30 13:03:00  208,064
2015-11-30 13:04:00  211,3066
2015-11-30 13:05:00  219,36
2015-11-30 13:06:00  233,4453
2015-11-30 13:07:00  232,8106
2015-11-30 13:08:00  217,872

StartRecordTime                 EndRecordTime              AVG(ValueAsreal)
2015-11-30 13:03:00             2015-11-30 13:08:00        220.476

Best Answer

You can find the min value for RecordTime where there are 6 consecutive rows by using a correlated query doing top(6) and then count the number of rows that is greater than or equal to 200.

Use that min value for RecordTime against the table to get the 6 consecutive rows that you want.

-- Setup table to test on
declare @T table
(
  RecordTime datetime primary key,
  ValueAsReal decimal(7, 4)
);

-- Add some data
insert into @T(RecordTime, ValueAsReal) 
select '2015-11-30 12:59:00',  195.6907 union all
select '2015-11-30 13:00:00',  203.8667 union all
select '2015-11-30 13:01:00',  198.0907 union all
select '2015-11-30 13:02:00',  198.3573 union all
select '2015-11-30 13:03:00',  208.064  union all
select '2015-11-30 13:04:00',  211.3066 union all
select '2015-11-30 13:05:00',  219.36   union all
select '2015-11-30 13:06:00',  233.4453 union all
select '2015-11-30 13:07:00',  232.8106 union all
select '2015-11-30 13:08:00',  217.872  union all
select '2015-11-30 13:09:00',  214.464  union all
select '2015-11-30 13:10:00',  200.0587 union all
select '2015-11-30 13:11:00',  173.776  union all
select '2015-11-30 13:12:00',  181.1787;

-- Desired number of rows
declare @NumRows int;
set @NumRows = 6;

-- Min value to look for
declare @ValueAsReal decimal(7, 4);
set @ValueAsReal = 200;


-- Get the first @NumRows rows where RecordTime is greater than or equal to 
-- the min value for RecordTime where there are 6
-- consecutive values for ValueAsReal greater than or equal to @ValueAsReal
select top(@NumRows) 
  T.RecordTime,
  T.ValueAsReal
from @T as T
where T.RecordTime >= (
                      select top(1) T1.RecordTime
                      from @T as T1
                      where (
                            select count(*)
                            from (
                                 select top(@NumRows) 
                                   T2.RecordTime, 
                                   T2.ValueAsReal
                                 from @T as T2
                                 where T1.RecordTime <= T2.RecordTime
                                 order by T2.RecordTime
                                 ) as T3
                            where T3.ValueAsReal >= @ValueAsReal
                            ) = @NumRows and
                            T1.ValueAsReal >= @ValueAsReal
                      order by T1.RecordTime
                      )
order by T.RecordTime;

Query plan:

Related Solutions

SQL Server – Finding Most Recent Row Without Exact Date Match

How about the following query? This query will take the exchange on the query date if it exists. If not, it will find the most recent exchange date prior to the query date and use that date's exchange rate.

DECLARE @Query_currency VARCHAR(3) = 'SGD',
        @Query_date DATETIME = '2015-09-05 00:00:00'
SELECT TOP(1) Currency, Exch_date AS Observed_exch_date,
    @Query_date AS Exch_date, Exch_value
FROM ExchRate
-- Find the exchange rate on the query date for this currency
-- If there isn't one, find the most recent exchange rate
WHERE Currency = @Query_currency
    AND Exch_date <= @Query_date
ORDER BY Observed_exch_date DESC

Looking up exchange rates in batch

If you need to find the exchange rate for multiple rows at a time, you can use CROSS APPLY to find the most recent exchange rate for each row. If you make sure that you have an appropriate index on (Currency, Exch_date) that includes the Exch_value column, looking up the exchange rate for each row will perform a single seek with no additional sort needed. You can see a full demonstration in this SQL Fiddle.

SELECT p.PO_Num, p.PO_Cur, p.PO_Date, e.Exch_date, e.Exch_value
FROM PO p
CROSS APPLY (
    -- For each PO, seek to the current or most recent exchange rate
    SELECT TOP 1 ex.Exch_date, ex.Exch_value
    FROM ExchRate ex
    WHERE ex.Currency = p.PO_Cur
      AND ex.Exch_date <= p.PO_Date
    ORDER BY ex.Exch_date DESC
) e

SQL Server T-SQL – Find Partial Duplicates of a Field

You can identify the repeated 1->8 substrings using this:

SELECT LEFT(basic.MEMBNO,8)
FROM dbo.basic
GROUP BY LEFT(basic.MEMBNO,8)
HAVING COUNT(*) > 1;

So you can join with that to get the individual values:

;WITH x(m) AS 
(
  SELECT LEFT(basic.MEMBNO,8)
  FROM dbo.basic
  GROUP BY LEFT(basic.MEMBNO,8)
  HAVING COUNT(*) > 1
)
SELECT b.MEMBNO
  FROM dbo.basic AS b
  INNER JOIN x
  ON x.m = LEFT(b.MEMBNO, 8)
  ORDER BY b.MEMBNO;

You can also do it this way (I just wasn't sure when windowed COUNT was introduced):

;WITH x AS 
( 
  SELECT MEMBNO, c = COUNT(*) OVER (PARTITION BY LEFT(MEMBNO, 8))
  FROM dbo.basic
)
SELECT MEMBNO FROM x WHERE c > 1
ORDER BY MEMBNO;

Best Answer

Related Solutions

SQL Server – Finding Most Recent Row Without Exact Date Match

SQL Server T-SQL – Find Partial Duplicates of a Field

Related Question