Clash Royale CLAN TAG#URR8PPP

SQL Convert Pivot results to H:mm:ss

This is what my Original Db looks like:

+-------------+----------+---------------------+------+
| StationName | State | Start_time | Time |
+-------------+----------+---------------------+------+
| P3 | Downtime | 2018-02-14 13:57:39 | 83 |
+-------------+----------+---------------------+------+
| P4 | Downtime | 2018-02-14 13:57:39 | 118 |
+-------------+----------+---------------------+------+
| P3 | Downtime | 2018-02-15 06:52:51 | 6 |
+-------------+----------+---------------------+------+
| P4 | Downtime | 2018-02-15 06:52:51 | 11 |
+-------------+----------+---------------------+------+
| P2 | Downtime | 2018-02-15 07:04:16 | 60 |
+-------------+----------+---------------------+------+
| P8 | Downtime | 2018-02-15 07:04:16 | 7 |
+-------------+----------+---------------------+------+


This is the code I use to achieve my current results:

SELECT * FROM(
SELECT CAST([start_time] as DATE) AS [DATE],
State,
Sum(Time) AS Downtime,
STATIONNAME

FROM A6K_Events

Where StationName Like 'P%' AND State='Downtime' AND (Dep != 'Event Log
Error' OR Dep IS NULL)
GROUP BY [start_time],STATIONNAME, State
) AS S
PIVOT (sum(Downtime) FOR STATIONNAME IN([P1],[P2],[P3],[P4],[P5],[P6],[P7],
[P8],[P9],[P10])) AS PT


My current results are:

+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| DATE | State | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| 2018-02-14 | Downtime | NULL | NULL | 83 | 118 | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| 2018-02-15 | Downtime | NULL | 60 | 6 | 11 | NULL | NULL | NULL | 7 | NULL | NULL |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+

+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| DATE | State | P1 | P2 | P3 | P4 | P5 | P6 | P7 | P8 | P9 | P10 |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| 2018-02-14 | Downtime | NULL | NULL | 83 | 118 | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+
| 2018-02-15 | Downtime | NULL | 60 | 6 | 11 | NULL | NULL | NULL | 7 | NULL | NULL |
+------------+----------+------+------+----+-----+------+------+------+------+------+------+

I have my tried many different placements and methods to convert this into minute seconds. I end up running into an array of errors and I am not sure what I am doing wrong. The method I use for turning seconds into hours mins seconds is below. I am not sure if this is not compatible with the formatting I am using or if I am putting it in the right spot or now. the column references in this Convert function below could be inaccurate too.

(CONVERT(varchar(6), sum(time)/3600) + ':' +
RIGHT('0' + CONVERT(varchar(2), (sum(time) % 3600) / 60), 2)+ ':' +
RIGHT('0' + CONVERT(varchar(2), sum(time) % 60), 2)) AS DowntimeMinSec,


Does anyone know of a good way to accomplish my goal of displaying the sums of downtime per day per process in hours minutes seconds? My "current results" pasted text table is exactly what I need just for the sums not to be in the seconds format. Any help I would greatly appreciate, thanks.

Edit: this was marked as a duplicate of a post that has nothing to do with pivoting. This post is about converting appropriately while using pivot not just generally converting time. My post also does not include anything to do with milliseconds. I would appreciate it if this was marked as not a duplicate because it is a different situation in which I was struggling with while already understanding the concepts that this post was marked as a duplicate of.

My expected output is my current result but in H:mm:ss

Edit For MR. Tab: Redo of attempts and errors using the methods from post that he marked a duplicate of mine

1

SELECT CONVERT(varchar, DATEADD(ms, sum(downtime), 0), 108), * FROM(
SELECT CAST([start_time] as DATE) AS [DATE],
State,
Sum(Time) AS Downtime,
STATIONNAME

FROM A6K_Events

Where StationName Like 'P%' AND State='Downtime' AND (Dep != 'Event Log
Error' OR Dep IS NULL)
GROUP BY [start_time],STATIONNAME, State
) AS S
PIVOT (sum(Downtime) FOR STATIONNAME IN([P1],[P2],[P3],[P4],[P5],[P6],[P7],
[P8],[P9],[P10])) AS PT

GatewayException: Invalid column name 'downtime'.
caused by SQLServerException: Invalid column name 'downtime'.


2

SELECT * FROM(
SELECT CAST([start_time] as DATE) AS [DATE],
State,
Sum(Time) AS Downtime,
STATIONNAME

FROM A6K_Events

Where StationName Like 'P%' AND State='Downtime' AND (Dep != 'Event Log
Error' OR Dep IS NULL)
GROUP BY [start_time],STATIONNAME, State
) AS S
PIVOT (CONVERT(varchar, DATEADD(ms, sum(downtime), 0), 108)) FOR STATIONNAME
IN([P1],[P2],[P3],[P4],[P5],[P6],[P7],[P8],[P9],[P10])) AS PT

GatewayException: Incorrect syntax near the keyword 'CONVERT'.
caused by SQLServerException: Incorrect syntax near the keyword 'CONVERT'.


3

SELECT CONVERT(varchar, DATEADD(ms, sum(time), 0), 108), * FROM(
SELECT CAST([start_time] as DATE) AS [DATE],
State,
Sum(Time) AS Downtime,
STATIONNAME

FROM A6K_Events

Where StationName Like 'P%' AND State='Downtime' AND (Dep != 'Event Log
Error' OR Dep IS NULL)
GROUP BY [start_time],STATIONNAME, State
) AS S
PIVOT (sum(Downtime) FOR STATIONNAME IN([P1],[P2],[P3],[P4],[P5],[P6],[P7],
[P8],[P9],[P10])) AS PT

GatewayException: Invalid column name 'time'.
caused by SQLServerException: Invalid column name 'time'.


2 Answers
2

Can you try this following query:

SELECT * FROM(
SELECT CAST([start_time] as DATETIME2) AS [DATE],
State,
Sum(Time) AS Downtime,
STATIONNAME

FROM A6K_Events

Where StationName Like 'P%' AND State='Downtime'
AND (Dep != 'Event LogError' OR Dep IS NULL)
GROUP BY [start_time],STATIONNAME, State
) AS S
PIVOT (sum(Downtime) FOR STATIONNAME IN([P1],[P2],[P3],[P4],[P5],[P6],[P7],
[P8],[P9],[P10])) AS PT


Output:

Ross Bush in the comments pointed me in the direction I was trying to go thank you everyone for your responses. In my outer query I needed to reference the columns which were pivoted when converting as opposed to referencing the previous name of the column.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.