X-Received: by 2002:ac8:1710:: with SMTP id w16-v6mr25245357qtj.42.1525793709607; Tue, 08 May 2018 08:35:09 -0700 (PDT) X-Received: by 2002:a1f:8253:: with SMTP id e80-v6mr6482035vkd.14.1525793709243; Tue, 08 May 2018 08:35:09 -0700 (PDT) Path: csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!nntp.giganews.com!x25-v6no84651qto.0!news-out.google.com!p41-v6ni103qtp.1!nntp.google.com!x25-v6no84645qto.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Tue, 8 May 2018 08:35:09 -0700 (PDT) In-Reply-To: <908baff0-4cf5-4482-8d50-96ed8dfaf702@googlegroups.com> 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> <9261dd0f-8f04-4810-a19a-a210e4bd9ad6@googlegroups.com> <908baff0-4cf5-4482-8d50-96ed8dfaf702@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <258d13b4-0580-4c01-8188-2d2470f7ca94@googlegroups.com> Subject: Re: How to add a datetime column for a 24 hour period From: Mike Arney Injection-Date: Tue, 08 May 2018 15:35:09 +0000 Content-Type: text/plain; charset="UTF-8" Lines: 134 Xref: csiph.com microsoft.public.sqlserver.programming:31339 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