Get all rows between start and end flag

Question

I've got a similar data structure

Parameter | Value | DateTime
----------------------------
Switch    | "on"  | 2019-10-13 15:01:25
Temp      | 25    | 2019-10-13 15:01:37
Pressure  | 1006  | 2019-10-13 15:01:53
...
Temp      | 22    | 2019-10-13 15:04:41
Switch    | "off" | 2019-10-13 15:04:59
...
Switch    | "on"  | 2019-10-13 17:14:51
Temp      | 27    | 2019-10-13 17:15:07
...
Switch    | "off" | 2019-10-13 17:17:43

Between each pair of Switch "on" and "off" I have to calculate the values for the parameters, i.e. average or max/min and so on. How can I get the different data sets to have multiple groups for the calculation?

I think this should be solvable with
- Stored Procedure (statement?)
- SSIS package (how?)
- .NET application.

What might be the best way to solve this issue?

Thanks in advance.

Update

This is the full structure of the table.

CREATE TABLE [schema].[foo]
(
    [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    [Group] VARCHAR(20) NOT NULL,
    [Parameter] VARCHAR(50) NOT NULL,
    [Type] VARCHAR(50) NOT NULL,
    [Timestamp] DATETIME NOT NULL,
    [Value] NVARCHAR(255) NOT NULL,
    [Unit] VARCHAR(10) NOT NULL,

    // Only for logging. No logic for the use case.
    [InsertedTimestampUtc] DATETIME NOT NULL DEFAULT(GetUtcDate()),
    [IsProcessed] INT NOT NULL DEFAULT(0)
)

Answer 1

If I understand your question correctly, the next approach may help to get the expected results:

Table:

CREATE TABLE #Data (
    [DateTime] datetime,
    [Parameter] varchar(50),
    [Value] varchar(10)
)
INSERT INTO #Data
    ([DateTime], [Parameter], [Value])
VALUES
    ('2019-10-13T15:01:25', 'Switch',   'on'),
    ('2019-10-13T15:01:37', 'Temp',     '25'),
    ('2019-10-13T15:01:53', 'Pressure', '1006'),
    ('2019-10-13T15:04:41', 'Temp',     '22'),
    ('2019-10-13T15:04:59', 'Switch',   'off'),
    ('2019-10-13T17:14:51', 'Switch',   'on'),
    ('2019-10-13T17:15:07', 'Temp',     '27'),
    ('2019-10-13T17:17:43', 'Switch',   'off')

Statement:

;WITH ChangesCTE AS (
    SELECT
        *,
        CASE WHEN [Parameter] = 'Switch' AND [Value] = 'on' THEN 1 ELSE 0 END AS ChangeIndex
    FROM #Data
), GroupsCTE AS (
    SELECT
        *,
        SUM(ChangeIndex) OVER (ORDER BY [DateTime]) AS GroupIndex
    FROM ChangesCTE
)
SELECT [GroupIndex], [Parameter], AVG(TRY_CONVERT(int, [Value]) * 1.0) AS [AvgValue]
FROM GroupsCTE
WHERE [Parameter] <> 'Switch'
GROUP BY [GroupIndex], [Parameter]

Results:

GroupIndex  Parameter   AvgValue
1           Pressure    1006.000000
1           Temp        23.500000
2           Temp        27.000000