How to add a datetime column for a 24 hour period

Discussion:

(too old to reply)

Mike Arney

2017-10-23 18:42:53 UTC

I have a MSSQL table with the following columns and data types:

Topic -- nvarchar
Payload -- numeric
Timestamp -- datetime
id -- int

Here is some example data:

Topic Payload Timestamp id
STG/A/CycleStart 0 2017-10-23 15:37:56.697 1651
STG/A/CycleStart 0 2017-10-23 15:37:55.670 1650
STG/A/CycleStart 0 2017-10-23 15:37:55.193 1649
STG/A/CycleStart 0 2017-10-23 15:37:54.687 1648

The Timestamp column's default value is set to: (getutcdate())

I need to add the TIMESTAMP column for the last 24 hours where TOPIC = 'STG/A/CycleStart' and PAYLOAD = 1

I also need this time in seconds.

Any help would be greatly appreciated.

Thanks,
Mike

Erland Sommarskog

2017-10-23 21:51:51 UTC

Permalink

Post by Mike Arney
Topic -- nvarchar
Payload -- numeric
Timestamp -- datetime
id -- int
Topic Payload Timestamp id
STG/A/CycleStart 0 2017-10-23 15:37:56.697 1651
STG/A/CycleStart 0 2017-10-23 15:37:55.670 1650
STG/A/CycleStart 0 2017-10-23 15:37:55.193 1649
STG/A/CycleStart 0 2017-10-23 15:37:54.687 1648
The Timestamp column's default value is set to: (getutcdate())
I need to add the TIMESTAMP column for the last 24 hours where TOPIC =
'STG/A/CycleStart' and PAYLOAD = 1
I also need this time in seconds.

Too bad then that the sample data only has payload = 0.

The best way to get help with this type of question is to post
1) CREATE TABLE statements for your table(s), preferrably simplified
to focus at the problem at hand.
2) INSERT statments with sample data.
3) The expected results given the sample.
4) A short description of the business rules that explains why you want
that particular result.
5) Which version of SQL Server you are using.

From the information you have posted so far, I am not prepare to make a
guess about what you are looking for.

--
Erland Sommarskog, Stockholm, ***@sommarskog.se

Mike Arney

2017-10-24 12:10:07 UTC

Permalink

Post by Erland Sommarskog

Too bad then that the sample data only has payload = 0.
The best way to get help with this type of question is to post
1) CREATE TABLE statements for your table(s), preferrably simplified
to focus at the problem at hand.
2) INSERT statments with sample data.
3) The expected results given the sample.
4) A short description of the business rules that explains why you want
that particular result.
5) Which version of SQL Server you are using.
From the information you have posted so far, I am not prepare to make a
guess about what you are looking for.
--

Sorry Erland,

I will try to explain better.

[code]
Create Table MQTTData(
Topic nvarchar(256),
Payload numeric(18,0),
Timestamp datetime Default GetUTCDate(),
id int Identity(1,1) Primary Key)

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)

WaitFor Delay '00:00:02'

Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)
[/code]

I need to SUM all Timestamp (in seconds) with a Topic of 'STG/A/CycleStart', a Payload of 1.

Payload of 1 = Machine On
Payload of 0 = Machine Off

I am trying to get amount of time that a Payload is = 1.

I am using SQL 2014 Enterprise.

Thanks,
Mike

Mike Arney

2017-10-24 12:12:57 UTC

Permalink

Post by Mike Arney

Post by Erland Sommarskog

Too bad then that the sample data only has payload = 0.
The best way to get help with this type of question is to post
1) CREATE TABLE statements for your table(s), preferrably simplified
to focus at the problem at hand.
2) INSERT statments with sample data.
3) The expected results given the sample.
4) A short description of the business rules that explains why you want
that particular result.
5) Which version of SQL Server you are using.
From the information you have posted so far, I am not prepare to make a
guess about what you are looking for.
--

Sorry Erland,
I will try to explain better.
[code]
Create Table MQTTData(
Topic nvarchar(256),
Payload numeric(18,0),
Timestamp datetime Default GetUTCDate(),
id int Identity(1,1) Primary Key)
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 0)
WaitFor Delay '00:00:02'
Insert Into MQTTData (Topic,Payload)
Values ('STG/A/CycleStart', 1)
[/code]
I need to SUM all Timestamp (in seconds) with a Topic of 'STG/A/CycleStart', a Payload of 1.
Payload of 1 = Machine On
Payload of 0 = Machine Off
I am trying to get amount of time that a Payload is = 1.
I am using SQL 2014 Enterprise.
Thanks,
Mike

Forgot to show sample data...

Topic Payload Timestamp id
STG/A/CycleStart 1 2017-10-24 12:00:37.160 1
STG/A/CycleStart 0 2017-10-24 12:00:39.160 2
STG/A/CycleStart 1 2017-10-24 12:00:41.160 3
STG/A/CycleStart 0 2017-10-24 12:00:43.160 4
STG/A/CycleStart 1 2017-10-24 12:00:45.163 5
STG/A/CycleStart 0 2017-10-24 12:00:47.167 6
STG/A/CycleStart 1 2017-10-24 12:00:49.167 7

Mike Arney

2017-10-24 12:38:29 UTC

Permalink

Post by Erland Sommarskog

Too bad then that the sample data only has payload = 0.
The best way to get help with this type of question is to post
1) CREATE TABLE statements for your table(s), preferrably simplified
to focus at the problem at hand.
2) INSERT statments with sample data.
3) The expected results given the sample.
4) A short description of the business rules that explains why you want
that particular result.
5) Which version of SQL Server you are using.
From the information you have posted so far, I am not prepare to make a
guess about what you are looking for.
--

Erland Sommarskog

2017-10-24 18:49:55 UTC

Permalink

Post by Mike Arney
I need to SUM all Timestamp (in seconds) with a Topic of
'STG/A/CycleStart', a Payload of 1.
Payload of 1 = Machine On
Payload of 0 = Machine Off
I am trying to get amount of time that a Payload is = 1 over the last 24 hours.

Here is a query. However, it is clear to how you want to handle the case
that the last row has Payload = 1. Should we count from that time until
"now", or is there something else? In the example I am assuming "now",
but you can change that as you see fit.

LEAD returns the next row in the result set as defined by the OVER clause.

; WITH ontimes AS (
SELECT Topic,
CASE WHEN Payload = 1
THEN datediff(ss, Timestamp,
LEAD(Timestamp, 1, sysutcdatetime())
OVER (PARTITION BY Topic ORDER BY Timestamp))
ELSE 0
END AS ontime
FROM MQTTData
)
SELECT Topic, SUM(ontime)
FROM ontimes
GROUP BY Topic

--
Erland Sommarskog, Stockholm, ***@sommarskog.se

Mike Arney

2017-10-25 11:57:59 UTC

Permalink

Post by Erland Sommarskog

Here is a query. However, it is clear to how you want to handle the case
that the last row has Payload = 1. Should we count from that time until
"now", or is there something else? In the example I am assuming "now",
but you can change that as you see fit.
LEAD returns the next row in the result set as defined by the OVER clause.
; WITH ontimes AS (
SELECT Topic,
CASE WHEN Payload = 1
THEN datediff(ss, Timestamp,
LEAD(Timestamp, 1, sysutcdatetime())
OVER (PARTITION BY Topic ORDER BY Timestamp))
ELSE 0
END AS ontime
FROM MQTTData
)
SELECT Topic, SUM(ontime)
FROM ontimes
GROUP BY Topic
--

Erland,
Thank you for this query! You are correct in assuming "NOW" as the count time. I trigger this query every 5 minutes, to populate a gauge on my UI. I have two gauges. One of them shows the "OnTime" for the last 24 hours and the other is the last 8 hours.

How can I get the last 24 hours of "OnTime" from "NOW"?

Thank you,
Mike Arney

Erland Sommarskog

2017-10-26 18:58:26 UTC

Permalink

Post by Mike Arney
How can I get the last 24 hours of "OnTime" from "NOW"?

Timestamp > dateadd(HOUR, -24, sysdatetime())

--
Erland Sommarskog, Stockholm, ***@sommarskog.se

Mike Arney

2017-11-02 16:54:39 UTC

Permalink

Post by Erland Sommarskog

Post by Mike Arney
How can I get the last 24 hours of "OnTime" from "NOW"?

Timestamp > dateadd(HOUR, -24, sysdatetime())
--

Thank you Erland! This was a tremendous help for my project.

Mike

m***@gmail.com

2018-05-08 15:21:45 UTC

Permalink

Hope you are still monitoring this, Erland...
I have made several changes to my database and now need to track each column's time. I can do them individually with your code, but can't figure out how to iterate through the rest of the columns.

Here is my example database:
[code]
Create Table MachineDataTest(
Machine nvarchar(256),
InCycle numeric(18,0),
R1 numeric(18,0),
R2 numeric(18,0),
R3 numeric(18,0),
R4 numeric(18,0),
R5 numeric(18,0),
Uncategorized numeric(18,0),
Timestamp datetime Default GetUTCDate(),
id int Identity(1,1) Primary Key)

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,0,1)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,1,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,1,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,1,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,1,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,1,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,1,0,0,0)

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,0,1)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,1,0,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,1,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,1,0,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,1,0,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,1,0,0)

WaitFor Delay '00:00:02'

Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,1,0,0,0)

[/code]

Here is the code that I used for InCycle time:

WITH InCycles AS
(SELECT Machine,
CASE WHEN InCycle = 1
THEN datediff(ss, Timestamp,
LEAD(Timestamp, 1, sysutcdatetime())
OVER (PARTITION BY Machine ORDER BY Timestamp))
ELSE 0
END AS InCycle
FROM MachineData Where Timestamp >= DATEADD(hour, -24, sysutcdatetime()))
SELECT SUM(InCycle) as InCycle FROM InCycles where Machine = 'LH35N-3000B';

I need to see (all times are examples):

Reason Time(in Seconds)
InCycle 67
R1 50
R2 39
R3 27
R4 20
R5 14
Uncategorized 2

I'm still using SQL server 2014.

Thanks,
Mike

Mike Arney

2018-05-08 15:35:09 UTC

Permalink

Post by m***@gmail.com
Hope you are still monitoring this, Erland...
I have made several changes to my database and now need to track each column's time. I can do them individually with your code, but can't figure out how to iterate through the rest of the columns.
[code]
Create Table MachineDataTest(
Machine nvarchar(256),
InCycle numeric(18,0),
R1 numeric(18,0),
R2 numeric(18,0),
R3 numeric(18,0),
R4 numeric(18,0),
R5 numeric(18,0),
Uncategorized numeric(18,0),
Timestamp datetime Default GetUTCDate(),
id int Identity(1,1) Primary Key)
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,0,1)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,1,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,1,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,1,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,1,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,1,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,1,0,0,0)
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,0,1)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,1,0,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,0,1,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,1,0,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,1,0,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 0,0,0,0,1,0,0)
WaitFor Delay '00:00:02'
Insert Into MachineDataTest(Machine,InCycle,R1,R2,R3,R4,R5,Uncategorized)
Values ('LH35N-3000B', 1,0,0,1,0,0,0)
[/code]
WITH InCycles AS
(SELECT Machine,
CASE WHEN InCycle = 1
THEN datediff(ss, Timestamp,
LEAD(Timestamp, 1, sysutcdatetime())
OVER (PARTITION BY Machine ORDER BY Timestamp))
ELSE 0
END AS InCycle
FROM MachineData Where Timestamp >= DATEADD(hour, -24, sysutcdatetime()))
SELECT SUM(InCycle) as InCycle FROM InCycles where Machine = 'LH35N-3000B';
Reason Time(in Seconds)
InCycle 67
R1 50
R2 39
R3 27
R4 20
R5 14
Uncategorized 2
I'm still using SQL server 2014.
Thanks,
Mike

Erland Sommarskog

2018-05-08 17:48:06 UTC

Permalink

Post by m***@gmail.com
WITH InCycles AS
(SELECT Machine,
CASE WHEN InCycle = 1
THEN datediff(ss, Timestamp,
LEAD(Timestamp, 1, sysutcdatetime())
OVER (PARTITION BY Machine ORDER BY Timestamp))
ELSE 0
END AS InCycle
FROM MachineData Where Timestamp >= DATEADD(hour, -24,

sysutcdatetime()))

Post by m***@gmail.com
SELECT SUM(InCycle) as InCycle FROM InCycles where Machine = 'LH35N-

3000B';

Post by m***@gmail.com
Reason Time(in Seconds)
InCycle 67
R1 50
R2 39
R3 27
R4 20
R5 14
Uncategorized 2
I'm still using SQL server 2014.

It's great that you post sample data, but when the expected output is only
examples, I'm still in the dark. So either you describe you what you are
looking for, or you provide expected output given the sample data you post.

It seems that you will need an unpivot operation, that much I can tell. But
how those times are to be computed, I don't know.

...and, no, don't expect me to go back check what you posted earlier in the
thread. That information may not be accurate anymore if your project has
moved on.

Mike Arney

2018-05-09 13:31:19 UTC

Permalink

Thank you for taking a look. I'm not really sure how to ask any differently, but I'll give it a shot.

I have (7) "Reason":
InCycle, R1, R2, R3, R4, R5, Uncategorized

I need the timestamps added, as "TotalTime", for each "Reason" when it equals 1.

You provided a very useful code to get one case, but I need all 6 cases returned.

I found a temporary solution, in my program, that runs 7 separate queries (one for each "Reason") and outputs it to a chart node. I would like one query to replace the 7 that I am currently running.

This is what I am running:

WITH incycles AS (SELECT Machine, CASE WHEN InCycle = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS incycle FROM MachineData Where Timestamp >= DATEADD(day, -1, sysutcdatetime()) and Timestamp < dateadd(day, -0, sysutcdatetime())) SELECT SUM(incycle) as InCycle FROM incycles where Machine = 'LH35N-3000B';

WITH r1s AS (SELECT Machine, CASE WHEN R1 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r1 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r1) as SetChange FROM r1s where Machine = 'LH35N-3000B';

WITH r2s AS (SELECT Machine, CASE WHEN R2 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r2 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r2) as ToolChange FROM r2s where Machine = 'LH35N-3000B';

WITH r3s AS (SELECT Machine, CASE WHEN R3 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r3 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r3) as Quality FROM r3s where Machine = 'LH35N-3000B';

WITH r4s AS (SELECT Machine, CASE WHEN R4 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r4 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r4) as NoOperator FROM r4s where Machine = 'LH35N-3000B';

WITH r5s AS (SELECT Machine, CASE WHEN R5 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r5 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r5) as BreakDown FROM r5s where Machine = 'LH35N-3000B';

WITH uncats AS (SELECT Machine, CASE WHEN Uncategorized = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS uncat FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(uncat) as Uncategorized FROM uncats where Machine = 'LH35N-3000B';

Thanks,
Mike

Mike Arney

2018-05-09 13:34:09 UTC

Permalink

Post by Mike Arney
InCycle, R1, R2, R3, R4, R5, Uncategorized
I need the timestamps added, as "TotalTime", for each "Reason" when it equals 1.
You provided a very useful code to get one case, but I need all 6 cases returned.
I found a temporary solution, in my program, that runs 7 separate queries (one for each "Reason") and outputs it to a chart node. I would like one query to replace the 7 that I am currently running.
WITH incycles AS (SELECT Machine, CASE WHEN InCycle = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS incycle FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(incycle) as InCycle FROM incycles where Machine = 'LH35N-3000B';
WITH r1s AS (SELECT Machine, CASE WHEN R1 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r1 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r1) as SetChange FROM r1s where Machine = 'LH35N-3000B';
WITH r2s AS (SELECT Machine, CASE WHEN R2 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r2 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r2) as ToolChange FROM r2s where Machine = 'LH35N-3000B';
WITH r3s AS (SELECT Machine, CASE WHEN R3 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r3 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r3) as Quality FROM r3s where Machine = 'LH35N-3000B';
WITH r4s AS (SELECT Machine, CASE WHEN R4 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r4 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r4) as NoOperator FROM r4s where Machine = 'LH35N-3000B';
WITH r5s AS (SELECT Machine, CASE WHEN R5 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r5 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r5) as BreakDown FROM r5s where Machine = 'LH35N-3000B';
WITH uncats AS (SELECT Machine, CASE WHEN Uncategorized = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS uncat FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(uncat) as Uncategorized FROM uncats where Machine = 'LH35N-3000B';
Thanks,
Mike

Erland Sommarskog

2018-05-09 21:08:28 UTC

Permalink

Post by Mike Arney
I found a temporary solution, in my program, that runs 7 separate
queries (one for each "Reason") and outputs it to a chart node. I would
like one query to replace the 7 that I am currently running.

The code is difficult to read because of poor formatting, but can't you
just have seven CASE expressions in the CTE, and then sum them all at the
same time?