2

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 | +------------+----------+------+------+----+-----+------+------+------+------+------+------+

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'.
tphasley
  • 55
  • 5
  • Could you also share expected result? – Arion Jul 31 '18 at 13:44
  • Expected result is the current result in h:mm:ss – tphasley Jul 31 '18 at 13:56
  • I had tried it previously but I will again, one moment. – tphasley Jul 31 '18 at 14:07
  • "I end up running into an array of errors " Edit your post and add the code you used that produced the errors, and the error message that was produced so that we can pinpoint what you need to change. – Tab Alleman Jul 31 '18 at 14:09
  • first issue is figuring out which of all of those answers on that page align properly with the format I'm using and how to filter out the ms portion of each and every solution in the comments. Then being I am not an expert hence why I asked the question in the first place I am not sure where to place the function, in my first select statemen?t or second? or does it go after my pivot statement? – tphasley Jul 31 '18 at 14:13
  • ok be prepared for my many different attempts at this and a scattered range of errors. – tphasley Jul 31 '18 at 14:14
  • It will be easier to help you if you put the code and errors in the question instead of the comments. Why not start with the code you tried in "The method I use for turning seconds into hours mins seconds is below."? What did that entire query look like and what error did you get? – Tab Alleman Jul 31 '18 at 14:18
  • working on it will continue to redo all my attempts from this morning and pasting the full query and errors using the methods from the duplicate page – tphasley Jul 31 '18 at 14:30
  • 1
    sum(downtime) has already been transposed into P1,P2,P3 by the time you try and access in the outer query. You can convert the actual columns though...P1=CAST(...P1), P2=CAST(..P2) – Ross Bush Jul 31 '18 at 14:30
  • Ok, I will work on that method right now thank you. – tphasley Jul 31 '18 at 14:34
  • Thank you Mr. Ross Bush you've steered me in the right direction. I lack some of the fundamentals and this helped me immensely. – tphasley Jul 31 '18 at 14:44

2 Answers2

1

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:

enter image description here

Community
  • 1
  • 1
Aura
  • 1,283
  • 2
  • 16
  • 30
  • Hello, yes i tried this just now. it did not result in what I was looking for but that is okay my post become a little confusing on what result I was looking for when it was marked as a duplicate and I had to prove to who marked it that it was not a duplicate. Ross Bush has pointed me in the direction I was looking to go sorry for the long and confusing post and your time spent on this solution but thank you for the help. – tphasley Jul 31 '18 at 14:48
  • 1
    Glad you are headed in the right direction. Happy to help! – Aura Jul 31 '18 at 14:49
0

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.

tphasley
  • 55
  • 5