X-Received: by 10.200.1.77 with SMTP id f13mr13973201qtg.4.1508847007888; Tue, 24 Oct 2017 05:10:07 -0700 (PDT)
X-Received: by 10.31.32.204 with SMTP id g195mr853958vkg.10.1508847007825; Tue, 24 Oct 2017 05:10:07 -0700 (PDT)
Path: csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!z50no1495785qtj.0!news-out.google.com!v14ni1530qtc.0!nntp.google.com!z50no1495782qtj.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups: microsoft.public.sqlserver.programming
Date: Tue, 24 Oct 2017 05:10:07 -0700 (PDT)
In-Reply-To: <XnsA817F2C2824FCYazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=216.234.125.194; posting-account=4nS5WwoAAACio8l9etd0ayIsc32V0GtK
NNTP-Posting-Host: 216.234.125.194
References: <d0c629ca-5826-4cd4-baba-9848a915df19@googlegroups.com> <XnsA817F2C2824FCYazorman@127.0.0.1>
User-Agent: G2/1.0
MIME-Version: 1.0
Message-ID: <cbaf62cb-2ced-49e7-bbe3-c2d49fdc64e0@googlegroups.com>
Subject: Re: How to add a datetime column for a 24 hour period
From: Mike Arney <mikespcnet.net@gmail.com>
Injection-Date: Tue, 24 Oct 2017 12:10:07 +0000
Content-Type: text/plain; charset="UTF-8"
Lines: 101
Xref: csiph.com microsoft.public.sqlserver.programming:31329

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