# 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:** 1\
**Showing post:** 8

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

---

_[View the full topic](https://forum.inductiveautomation.com/t/sql-query-group-data-in-table-2-by-time-events-in-table-1/58558)._