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

Groups > microsoft.public.sqlserver.programming > #31340

Re: How to add a datetime column for a 24 hour period

Show all headers | View raw

 (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.

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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