# SQL Query Group data in Table 2 by time events in Table 1

**URL:** <https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558>\
**Category:** Ignition\
**Created:** [April 18, 2022, 5:38pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558 "2022-04-18T17:38:54Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![beckya](https://avatars.discourse-cdn.com/v4/letter/b/b5a626/32.png) [@beckya](https://forum.inductiveautomation.com/u/beckya)\
**Post date:** [April 18, 2022, 5:38pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/1 "2022-04-18T17:38:54Z")

</div>

I have 2 tables, and I am trying to group the data in Table 2 using the time between Table 1 events, start time being output 1 and end time being output 0. I could use some SQL query help on how to write the query. I want to be able to sum the outputs in Table 2 to show high level what occurred between the start and end time of each event in Table 1 and also display the details but separate based on Table 1 events.

Table 1  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/b/4bf1f37b0bd2b5d01e2e7d3692b40726d778f079.png)

Table 2

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/c/ccc57a467f7116d5c07dcd6f24914a573a3a984c.png)

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 18, 2022, 6:53pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/2 "2022-04-18T18:53:14Z")

</div>

What SQL flavor are you using? MariaDB/MySQL, Postgres, MSSQL…

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 18, 2022, 7:13pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/3 "2022-04-18T19:13:47Z")

</div>

And what version of DB? MySQL v5 has limited tools for this kind of analysis.

---

<div class="post-metadata">

**Author:** ![beckya](https://avatars.discourse-cdn.com/v4/letter/b/b5a626/32.png) [@beckya](https://forum.inductiveautomation.com/u/beckya)\
**Post date:** [April 18, 2022, 7:59pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/4 "2022-04-18T19:59:57Z")

</div>

It’s a SQL Server version 15, MSSQL

---

<div class="post-metadata">

**Author:** ![beckya](https://avatars.discourse-cdn.com/v4/letter/b/b5a626/32.png) [@beckya](https://forum.inductiveautomation.com/u/beckya)\
**Post date:** [April 20, 2022, 6:02pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/5 "2022-04-20T18:02:09Z")

</div>

@pturmel or @dkhayes117 would you be able to help now that I provided the information you requested?

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [April 20, 2022, 6:54pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/6 "2022-04-20T18:54:03Z")

</div>

Curious how this will work if you can have 0 output entries with no corresponding 1 entry in Table 1.

---

<div class="post-metadata">

**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\
**Post date:** [April 20, 2022, 6:55pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/7 "2022-04-20T18:55:07Z")

</div>

I don’t know MSSQL, but I fiddled with it a bit in MySQL on [www.db-fiddle.com](http://www.db-fiddle.com). Take a look  
[DB Fiddle - SQL Database Playground](https://www.db-fiddle.com/f/8Lxrn9dcMYLLhZdLns7aLg/0)

```auto
Create table table1 (
  output tinyint,
  t_stamp datetime
);

Create table table2 (
  output tinyint,
  t_stamp datetime
);

INSERT INTO table1 (output, t_stamp) VALUES (1, '2022-04-16 03:28:47');
INSERT INTO table1 (output, t_stamp) VALUES (0, '2022-04-16 03:29:43');
INSERT INTO table1 (output, t_stamp) VALUES (1, '2022-04-16 03:30:42');
INSERT INTO table1 (output, t_stamp) VALUES (0, '2022-04-16 03:31:39');
INSERT INTO table1 (output, t_stamp) VALUES (0, '2022-04-17 04:44:00');
INSERT INTO table1 (output, t_stamp) VALUES (0, '2022-04-17 04:44:00');
INSERT INTO table1 (output, t_stamp) VALUES (1, '2022-04-18 05:08:07');

INSERT INTO table2 (output, t_stamp) VALUES (1, '2022-04-16 03:28:48');
INSERT INTO table2 (output, t_stamp) VALUES (1, '2022-04-16 03:28:55');
INSERT INTO table2 (output, t_stamp) VALUES (1, '2022-04-16 03:29:03');
INSERT INTO table2 (output, t_stamp) VALUES (0, '2022-04-16 03:29:06');
INSERT INTO table2 (output, t_stamp) VALUES (1, '2022-04-16 03:29:08');
INSERT INTO table2 (output, t_stamp) VALUES (0, '2022-04-16 03:29:11');
INSERT INTO table2 (output, t_stamp) VALUES (1, '2022-04-16 03:29:13');

```

```auto
select start, end, 
	(select ifnull(sum(t2.output),0) from table2 t2 where t2.t_stamp between start and end) as output
from
  (select t_stamp as start, LEAD(t_stamp,1) over (partition by output) as end
  from table1 
  where output = 1) t1;

```

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 20, 2022, 8:12pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/8 "2022-04-20T20:12:19Z")

</div>

I presume you want to ignore repeat ones and zeros in the output column? If so, running periods from table1 would be something like this:

```auto
Select t_stamp as start_ts,
  lead(t_stamp, 1, current_timestamp) Over (Order by t_stamp) as end_ts
From (
  Select t_stamp, output
  From (
    Select t_stamp,
      output,
      lag(output, 1, 0) Over (Order By t_stamp) as lag_out
    From table1
  ) innerq
  Where output != lag_out
) changesq
Where output = 1
Order By start_ts

```

And similarly, downtime periods from table2 would be something like this:

```auto
Select t_stamp as start_ts,
  lead(t_stamp, 1, current_timestamp) Over (Order by t_stamp) as end_ts
From (
  Select t_stamp, output
  From (
    Select t_stamp,
      output,
      lag(output, 1, 1) Over (Order By t_stamp) as lag_out
    From table2
  ) innerq
  Where output != lag_out
) changesq
Where output = 0
Order By start_ts

```

You would make a join of these two queries where the time spans overlap.

In each of those, `innerq` obtains the previous record’s `output` value and then `changesq` throws away the repeat rows. The outermost `Select` then produces time spans and throws away the intervening opposite states.

---

<div class="post-metadata">

**Author:** ![beckya](https://avatars.discourse-cdn.com/v4/letter/b/b5a626/32.png) [@beckya](https://forum.inductiveautomation.com/u/beckya)\
**Post date:** [April 21, 2022, 4:07pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/9 "2022-04-21T16:07:24Z")

</div>

Thank you @pturmel, this is working so far. May I ask what type of join would you use to get the time spans to overlap?

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [April 21, 2022, 4:55pm UTC](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558/10 "2022-04-21T16:55:27Z")

</div>

I would probably use a left join something like this:

```auto
Select runs.start_ts as run_start,
  runs.end_ts as run_end,
  downs.start_ts as down_start,
  downs.end_ts as down_end
From (.... nested table1 stuff ....) runs
Left Join (.... nested table2 stuff ....) downs
On runs.start_ts < downs.end_ts And downs.start_ts < runs.end_ts

```

A run that is uninterrupted will show up as one row with nulls for down\_start and down\_end. A run with multiple stops (including overlapping) will show a row for each stop. If you want to add up downtime only within each run, you will need to clip down\_start/end with a case statement to the limits of run\_start/end before you use sum().