Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31327 > unrolled thread

How to add a datetime column for a 24 hour period

Started byMike Arney <mikespcnet.net@gmail.com>
First post2017-10-23 11:42 -0700
Last post2018-05-09 23:08 +0200
Articles 15 — 3 participants

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-23 11:42 -0700
    Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-23 23:51 +0200
      Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:10 -0700
        Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:12 -0700
      Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-24 05:38 -0700
        Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-24 20:49 +0200
          Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-10-25 04:57 -0700
            Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2017-10-26 20:58 +0200
              Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2017-11-02 09:54 -0700
                Re: How to add a datetime column for a 24 hour period mikespcnet.net@gmail.com - 2018-05-08 08:21 -0700
                  Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-08 08:35 -0700
                  Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2018-05-08 19:48 +0200
                    Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-09 06:31 -0700
                      Re: How to add a datetime column for a 24 hour period Mike Arney <mikespcnet.net@gmail.com> - 2018-05-09 06:34 -0700
                      Re: How to add a datetime column for a 24 hour period Erland Sommarskog <esquel@sommarskog.se> - 2018-05-09 23:08 +0200

#31327 — How to add a datetime column for a 24 hour period

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-10-23 11:42 -0700
SubjectHow to add a datetime column for a 24 hour period
Message-ID<d0c629ca-5826-4cd4-baba-9848a915df19@googlegroups.com>
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

[toc] | [next] | [standalone]

#31328

FromErland Sommarskog <esquel@sommarskog.se>
Date2017-10-23 23:51 +0200
Message-ID<XnsA817F2C2824FCYazorman@127.0.0.1>
In reply to#31327
Mike Arney (mikespcnet.net@gmail.com) writes:
> 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.
> 

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, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#31329

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-10-24 05:10 -0700
Message-ID<cbaf62cb-2ced-49e7-bbe3-c2d49fdc64e0@googlegroups.com>
In reply to#31328
On Monday, October 23, 2017 at 5:51:54 PM UTC-4, Erland Sommarskog wrote:
> Mike Arney (mikespcnet.net@gmail.com) writes:
> > 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.
> > 
> 
> 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, esquel@sommarskog.se

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

[toc] | [prev] | [next] | [standalone]

#31330

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-10-24 05:12 -0700
Message-ID<7ca7e4e7-3b65-4eee-8982-00ddb4096e40@googlegroups.com>
In reply to#31329
On Tuesday, October 24, 2017 at 8:10:10 AM UTC-4, Mike Arney wrote:
> On Monday, October 23, 2017 at 5:51:54 PM UTC-4, Erland Sommarskog wrote:
> > Mike Arney (mikespcnet.net@gmail.com) writes:
> > > 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.
> > > 
> > 
> > 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, esquel@sommarskog.se
> 
> 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

[toc] | [prev] | [next] | [standalone]

#31331

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-10-24 05:38 -0700
Message-ID<9ebb76da-7445-4b19-a786-49e159c79594@googlegroups.com>
In reply to#31328
On Monday, October 23, 2017 at 5:51:54 PM UTC-4, Erland Sommarskog wrote:
> Mike Arney (mikespcnet.net@gmail.com) writes:
> > 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.
> > 
> 
> 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, esquel@sommarskog.se

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 over the last 24 hours.

Here is the 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

I am using SQL 2014 Enterprise. 

Thanks, 
Mike

[toc] | [prev] | [next] | [standalone]

#31332

FromErland Sommarskog <esquel@sommarskog.se>
Date2017-10-24 20:49 +0200
Message-ID<XnsA818D3EA567D3Yazorman@127.0.0.1>
In reply to#31331
Mike Arney (mikespcnet.net@gmail.com) writes:
> 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, esquel@sommarskog.se

[toc] | [prev] | [next] | [standalone]

#31334

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-10-25 04:57 -0700
Message-ID<d2765dc6-eb6b-46ef-9a89-d2f6c80d70f8@googlegroups.com>
In reply to#31332
On Tuesday, October 24, 2017 at 2:49:58 PM UTC-4, Erland Sommarskog wrote:
> Mike Arney (mikespcnet.net@gmail.com) writes:
> > 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, esquel@sommarskog.se

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

[toc] | [prev] | [next] | [standalone]

#31336

FromErland Sommarskog <esquel@sommarskog.se>
Date2017-10-26 20:58 +0200
Message-ID<XnsA81AD55BF8993Yazorman@127.0.0.1>
In reply to#31334
Mike Arney (mikespcnet.net@gmail.com) writes:
> How can I get the last 24 hours of "OnTime" from "NOW"?
> 

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

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

[toc] | [prev] | [next] | [standalone]

#31337

FromMike Arney <mikespcnet.net@gmail.com>
Date2017-11-02 09:54 -0700
Message-ID<9261dd0f-8f04-4810-a19a-a210e4bd9ad6@googlegroups.com>
In reply to#31336
On Thursday, October 26, 2017 at 2:58:29 PM UTC-4, Erland Sommarskog wrote:
> Mike Arney (mikespcnet.net@gmail.com) writes:
> > How can I get the last 24 hours of "OnTime" from "NOW"?
> > 
> 
> Timestamp > dateadd(HOUR, -24, sysdatetime())
> 
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

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

Mike

[toc] | [prev] | [next] | [standalone]

#31338

Frommikespcnet.net@gmail.com
Date2018-05-08 08:21 -0700
Message-ID<908baff0-4cf5-4482-8d50-96ed8dfaf702@googlegroups.com>
In reply to#31337
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

[toc] | [prev] | [next] | [standalone]

#31339

FromMike Arney <mikespcnet.net@gmail.com>
Date2018-05-08 08:35 -0700
Message-ID<258d13b4-0580-4c01-8188-2d2470f7ca94@googlegroups.com>
In reply to#31338
On Tuesday, May 8, 2018 at 11:21:50 AM UTC-4, Mike Arney wrote:
> 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

[toc] | [prev] | [next] | [standalone]

#31340

FromErland Sommarskog <esquel@sommarskog.se>
Date2018-05-08 19:48 +0200
Message-ID<XnsA8DCC96EF8844Yazorman@127.0.0.1>
In reply to#31338
 (mikespcnet.net@gmail.com) writes:
> 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.
> 

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.

[toc] | [prev] | [next] | [standalone]

#31341

FromMike Arney <mikespcnet.net@gmail.com>
Date2018-05-09 06:31 -0700
Message-ID<13d7936c-03c4-409a-b5cc-512d78ae8592@googlegroups.com>
In reply to#31340
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

[toc] | [prev] | [next] | [standalone]

#31342

FromMike Arney <mikespcnet.net@gmail.com>
Date2018-05-09 06:34 -0700
Message-ID<2502d74c-08c1-42c8-9331-43f9e40433b3@googlegroups.com>
In reply to#31341
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(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

[toc] | [prev] | [next] | [standalone]

#31343

FromErland Sommarskog <esquel@sommarskog.se>
Date2018-05-09 23:08 +0200
Message-ID<XnsA8DDEB67C63F8Yazorman@127.0.0.1>
In reply to#31341
Mike Arney (mikespcnet.net@gmail.com) writes:
> 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?

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web