X-Received: by 10.233.237.73 with SMTP id c70mr1349217qkg.54.1508932680048; Wed, 25 Oct 2017 04:58:00 -0700 (PDT) X-Received: by 10.31.168.133 with SMTP id r127mr158926vke.8.1508932680016; Wed, 25 Oct 2017 04:58:00 -0700 (PDT) Path: csiph.com!weretis.net!feeder6.news.weretis.net!feeder.usenetexpress.com!feeder-in1.iad1.usenetexpress.com!border1.nntp.dca1.giganews.com!nntp.giganews.com!z50no1749817qtj.0!news-out.google.com!v14ni369qtc.0!nntp.google.com!z50no1749813qtj.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Wed, 25 Oct 2017 04:57:59 -0700 (PDT) In-Reply-To: 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: <9ebb76da-7445-4b19-a786-49e159c79594@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: How to add a datetime column for a 24 hour period From: Mike Arney Injection-Date: Wed, 25 Oct 2017 11:58:00 +0000 Content-Type: text/plain; charset="UTF-8" Lines: 46 Xref: csiph.com microsoft.public.sqlserver.programming:31334 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