StackOverflow_53799678, Sql Server

StackOverflow_53799678

CREATE TABLE [TestTable](
    [ProjectID] [int] NOT NULL,
    [Index] [int] NOT NULL,
    [Voltage] [decimal](18, 3) NOT NULL,
    [Current] [decimal](18, 3) NOT NULL
);

INSERT INTO [TestTable] ([ProjectID], [Index], [Voltage], [Current]) VALUES
(1,           1,       2.3,         3.4), 
(1,           2,       2.5,         3.3),
(1,           3,       2.7,         3.0),
(1,           4,       2.8,         2.9),
(1,           5,       2.5,         3.1),
(1,           6,       2.0,         3.4),
(1,           7,       1.2,         3.5),
(1,           8,       0.5,         3.0),
(2,           1,       2.0,         1.0),
(2,           2,       5.0,         2.0),
(2,           3,       3.0,         2.0),
(2,           4,       1.0,         1.0);

SELECT tt.ProjectID, 
       MAX(tt.Voltage) AS MaxVoltage,
       x.StartIndex,
       MAX(tt.[Index]) AS EndIndex
FROM TestTable AS tt
JOIN 
(  
    SELECT ProjectID, 
           MIN([Index]) AS StartIndex
    FROM TestTable
    WHERE Voltage >= 2.5
    GROUP BY ProjectID 
) AS x ON tt.ProjectID = x.ProjectID 
WHERE tt.Voltage >= 1.5 
  AND tt.[Index] >= x.StartIndex
GROUP BY tt.ProjectID, x.StartIndex


run \| edit \| history \| help	0