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

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

Newsgroups	microsoft.public.sqlserver.programming
Date	2018-05-09 06:31 -0700
References	(4 earlier) <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> <XnsA8DCC96EF8844Yazorman@127.0.0.1>
Message-ID	<13d7936c-03c4-409a-b5cc-512d78ae8592@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

Thank you for taking a look. I'm not really sure how to ask any differently, but I'll give it a shot.

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(day, -1, sysutcdatetime()) and Timestamp < dateadd(day, -0, 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