Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.sqlserver.programming > #31339
| Newsgroups | microsoft.public.sqlserver.programming |
|---|---|
| Date | 2018-05-08 08:35 -0700 |
| References | (3 earlier) <XnsA818D3EA567D3Yazorman@127.0.0.1> <d2765dc6-eb6b-46ef-9a89-d2f6c80d70f8@googlegroups.com> <XnsA81AD55BF8993Yazorman@127.0.0.1> <9261dd0f-8f04-4810-a19a-a210e4bd9ad6@googlegroups.com> <908baff0-4cf5-4482-8d50-96ed8dfaf702@googlegroups.com> |
| Message-ID | <258d13b4-0580-4c01-8188-2d2470f7ca94@googlegroups.com> (permalink) |
| Subject | Re: How to add a datetime column for a 24 hour period |
| From | Mike Arney <mikespcnet.net@gmail.com> |
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
Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar
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
csiph-web