X-Received: by 2002:a0c:e9c6:: with SMTP id q6-v6mr28055311qvo.31.1525872850377; Wed, 09 May 2018 06:34:10 -0700 (PDT) X-Received: by 2002:a1f:8253:: with SMTP id e80-v6mr7072792vkd.14.1525872849953; Wed, 09 May 2018 06:34:09 -0700 (PDT) Path: csiph.com!xmission!news.snarked.org!border2.nntp.dca1.giganews.com!nntp.giganews.com!x25-v6no540225qto.0!news-out.google.com!k3-v6ni358qtj.0!nntp.google.com!x25-v6no540224qto.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Wed, 9 May 2018 06:34:09 -0700 (PDT) In-Reply-To: <13d7936c-03c4-409a-b5cc-512d78ae8592@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> <13d7936c-03c4-409a-b5cc-512d78ae8592@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <2502d74c-08c1-42c8-9331-43f9e40433b3@googlegroups.com> Subject: Re: How to add a datetime column for a 24 hour period From: Mike Arney Injection-Date: Wed, 09 May 2018 13:34:10 +0000 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Lines: 62 Xref: csiph.com microsoft.public.sqlserver.programming:31342 I have (7) "Reason": > InCycle, R1, R2, R3, R4, R5, Uncategorized >=20 > I need the timestamps added, as "TotalTime", for each "Reason" when it eq= uals 1. >=20 > You provided a very useful code to get one case, but I need all 6 cases r= eturned. >=20 > 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 q= uery to replace the 7 that I am currently running. >=20 > This is what I am running: >=20 >=20 > WITH incycles AS (SELECT Machine, CASE WHEN InCycle =3D 1 THEN datediff(s= s, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machi= ne ORDER BY Timestamp)) ELSE 0 END AS incycle FROM MachineData Where Timest= amp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(incycle) as InCycle = FROM incycles where Machine =3D 'LH35N-3000B'; >=20 > WITH r1s AS (SELECT Machine, CASE WHEN R1 =3D 1 THEN datediff(ss, Timesta= mp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER B= Y Timestamp)) ELSE 0 END AS r1 FROM MachineData Where Timestamp > DATEADD(h= our, -24, sysutcdatetime())) SELECT SUM(r1) as SetChange FROM r1s where Mac= hine =3D 'LH35N-3000B'; >=20 >=20 > WITH r2s AS (SELECT Machine, CASE WHEN R2 =3D 1 THEN datediff(ss, Timesta= mp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER B= Y Timestamp)) ELSE 0 END AS r2 FROM MachineData Where Timestamp > DATEADD(h= our, -24, sysutcdatetime())) SELECT SUM(r2) as ToolChange FROM r2s where Ma= chine =3D 'LH35N-3000B'; >=20 > WITH r3s AS (SELECT Machine, CASE WHEN R3 =3D 1 THEN datediff(ss, Timesta= mp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER B= Y Timestamp)) ELSE 0 END AS r3 FROM MachineData Where Timestamp > DATEADD(h= our, -24, sysutcdatetime())) SELECT SUM(r3) as Quality FROM r3s where Machi= ne =3D 'LH35N-3000B'; >=20 > WITH r4s AS (SELECT Machine, CASE WHEN R4 =3D 1 THEN datediff(ss, Timesta= mp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER B= Y Timestamp)) ELSE 0 END AS r4 FROM MachineData Where Timestamp > DATEADD(h= our, -24, sysutcdatetime())) SELECT SUM(r4) as NoOperator FROM r4s where Ma= chine =3D 'LH35N-3000B'; >=20 >=20 > WITH r5s AS (SELECT Machine, CASE WHEN R5 =3D 1 THEN datediff(ss, Timesta= mp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY Machine ORDER B= Y Timestamp)) ELSE 0 END AS r5 FROM MachineData Where Timestamp > DATEADD(h= our, -24, sysutcdatetime())) SELECT SUM(r5) as BreakDown FROM r5s where Mac= hine =3D 'LH35N-3000B'; >=20 > WITH uncats AS (SELECT Machine, CASE WHEN Uncategorized =3D 1 THEN datedi= ff(ss, Timestamp, LEAD(Timestamp, 1, sysutcdatetime()) OVER (PARTITION BY M= achine ORDER BY Timestamp)) ELSE 0 END AS uncat FROM MachineData Where Time= stamp > DATEADD(hour, -24, sysutcdatetime())) SELECT SUM(uncat) as Uncatego= rized FROM uncats where Machine =3D 'LH35N-3000B'; >=20 > Thanks, > Mike