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

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

Newsgroups	microsoft.public.sqlserver.programming
Date	2018-05-09 06:34 -0700
References	(5 earlier) <XnsA81AD55BF8993Yazorman@127.0.0.1> <9261dd0f-8f04-4810-a19a-a210e4bd9ad6@googlegroups.com> <908baff0-4cf5-4482-8d50-96ed8dfaf702@googlegroups.com> <XnsA8DCC96EF8844Yazorman@127.0.0.1> <13d7936c-03c4-409a-b5cc-512d78ae8592@googlegroups.com>
Message-ID	<2502d74c-08c1-42c8-9331-43f9e40433b3@googlegroups.com> (permalink)
Subject	Re: How to add a datetime column for a 24 hour period
From	Mike Arney <mikespcnet.net@gmail.com>

Show all headers | View raw

I have (7) "Reason":
> InCycle, R1, R2, R3, R4, R5, Uncategorized
> 
> I need the timestamps added, as "TotalTime", for each "Reason" when it equals 1.
> 
> You provided a very useful code to get one case, but I need all 6 cases returned.
> 
> I found a temporary solution, in my program, that runs 7 separate queries (one for each "Reason") and outputs it to a chart node. I would like one query to replace the 7 that I am currently running.
> 
> This is what I am running:
> 
> 
> 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';
> 
> WITH r1s AS (SELECT Machine, CASE WHEN R1 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r1 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r1) as SetChange FROM r1s where Machine = 'LH35N-3000B';
> 
> 
> WITH r2s AS (SELECT Machine, CASE WHEN R2 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r2 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r2) as ToolChange FROM r2s where Machine = 'LH35N-3000B';
> 
> WITH r3s AS (SELECT Machine, CASE WHEN R3 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r3 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r3) as Quality FROM r3s where Machine = 'LH35N-3000B';
> 
> WITH r4s AS (SELECT Machine, CASE WHEN R4 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r4 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r4) as NoOperator FROM r4s where Machine = 'LH35N-3000B';
> 
> 
> WITH r5s AS (SELECT Machine, CASE WHEN R5 = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS r5 FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(r5) as BreakDown FROM r5s where Machine = 'LH35N-3000B';
> 
> WITH uncats AS (SELECT Machine, CASE WHEN Uncategorized = 1 THEN datediff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER BY Timestamp)) ELSE 0 END AS uncat FROM MachineData Where Timestamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(uncat) as Uncategorized FROM uncats where Machine = 'LH35N-3000B';
> 
> Thanks,
> Mike

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