CODEWITHSUNDEEP
sql-servert-sqlpivot
musium
musium

Reputation: 3082

T-SQL Pivot table

I’ve a table MachineStatus which stores the status history of a machine. The table looks like this:

| MachineStatusId                        |   From              |   To                |   State |   MachineId                           |
----------------------------------------------------------------------------------------------------------------------------------------
|   B065FC43-DBE7-E611-9BDB-801F02F47041 | 2017-01-30 07:00:00 | 2017-01-30 08:00:00 |       1 | 92649C7B-E962-4EB1-B631-00086EECA98A  |
|   B165FC43-DBE7-E611-9BDB-801F02F47041 | 2017-01-30 08:00:00 | 2017-01-30 09:00:00 |     200 | 92649C7B-E962-4EB1-B631-00086EECA98A  |
|   B265FC43-DBE7-E611-9BDB-801F02F47041 | 2017-01-30 07:00:00 | 2017-01-30 08:00:00 |       1 | A2649C7B-E962-4EB1-B631-00086EECA98A  |
|   B365FC43-DBE7-E611-9BDB-801F02F47041 | 2017-01-30 08:00:00 | 2017-01-30 09:00:00 |     500 | A2649C7B-E962-4EB1-B631-00086EECA98A  |

It stores for each machine, for each status change a record with the information [From] when [To] when a certain [State] was valid. I like to calculate the time each machine spent in each state.

The result should look like this:

| MachineId                              |   Alias         |   State1 |   State200 |   State500 |
-------------------------------------------------------------------------------------------------
|   92649C7B-E962-4EB1-B631-00086EECA98A | Somename        |       60 |         60 |          0 |
|   A2649C7B-E962-4EB1-B631-00086EECA98A | Some other name |       60 |          0 |         60 |

Each state should be represented as a column.

Here is wat I have tried so far:

SELECT
    MAX(mState.MachineId),
    MAX(m.Alias),
    SUM(CASE mState.State WHEN 1 THEN mState.Diff ELSE 0 END) AS CritTime,
    SUM(CASE mState.State WHEN 200 THEN mState.Diff ELSE 0 END) AS OpTime,
    SUM(CASE mState.State WHEN 500 THEN mState.Diff ELSE 0 END) AS OtherTime
FROM 
    (
        SELECT
            DATEDIFF(MINUTE, ms.[From], ISNULL(ms.[To], GETDATE())) AS Diff,
            ms.State AS State,
            MachineId
        FROM
            MachineStatus ms
        WHERE
            ms.[From] >= @rangeFrom AND
            (ms.[To] <= @rangeEnd OR ms.[To] IS NULL)
    ) as mState

    INNER JOIN Machines m ON m.MachineId = mState.MachineId
GROUP BY
    mState.MachineId,
    m.Alias,
    mState.State

Calculating the time and grouping the result by machines works but I cannot figure out how to reduce the result set only contain one row per machine but with a column per state.

Upvotes: 0

Views: 65

Answers (1)

McNets
McNets

Reputation: 10827

I started in your subquery without apply any sum to your calculated data:

    SELECT m.MachineId,
           m.Alias,
           Minutes,
           s.State
    FROM machines m
         INNER JOIN states s ON m.MachineId = s.MachineId

Then you can pivot() for [State] and calculate the sum() of every state in this form:

WITH Calc AS
(
    SELECT m.MachineId,
           m.Alias,
           Minutes,
           s.State
    FROM machines m
         INNER JOIN states s ON m.MachineId = s.MachineId
)
SELECT MachineId, Alias, [State1], [State2], [State500]
FROM
(SELECT MachineId, Alias, State, Minutes FROM Calc) AS SourceTable
PIVOT
(
    SUM(Minutes) FOR State IN ([State1],[State2],[State500])
) AS PivotTable;

This is the result:

+--------------------------------------+---------+--------+--------+----------+
|               MachineId              |  Alias  | State1 | State2 | State500 |
+--------------------------------------+---------+--------+--------+----------+
| 92649C7B-E962-4EB1-B631-00086EECA98A | Alias 1 | 100    | 100    | 100      |
+--------------------------------------+---------+--------+--------+----------+
| A2649C7B-E962-4EB1-B631-00086EECA98A | Alias 2 | 10     | 20     | 70       |
+--------------------------------------+---------+--------+--------+----------+

Notice that you must know how many states return your data.

Can check it here: http://rextester.com/DHDX77489

Upvotes: 3

Related Questions