# Creating new dataset for Ignition reporting from two SQL queries

**URL:** <https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [October 20, 2023, 11:14am UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756 "2023-10-20T11:14:01Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 20, 2023, 11:14am UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/1 "2023-10-20T11:14:01Z")

</div>

Hello

I have a system which is saving timestamp for events (turning on/off a motor) in a database. I have two queries to get the timestamp of the events (one for 'start' event and other for 'stop' event). Now I want to make a report showing 'start' and 'stop' event timestamps in a table.

Below picture might explain what I am trying to achieve:

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

I have managed to write a script to create a new dataset from two queries. But it is not giving the result I am expecting. Can anyone help please?

Below is the picture of report I am getting:

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/5/85afbae3fc378ac17021b6bef0ec445bc49e3900.png)

Below is my script:

def updateData(data, sample):

```
start = system.dataset.toPyDataSet(data['Start'].getCoreResults())
stop = system.dataset.toPyDataSet(data['Stop'].getCoreResults())

headers = ['start', 'stop']
dataOut = []
for row in start:
	dataOut.append([row[0],None])
for row in stop:
	dataOut.append([None,row[0]])

combinedDataSet = system.dataset.toDataSet(headers, dataOut)
data['combinedData'] = system.dataset.sort(combinedDataSet, 0)

```

---

<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:** [October 20, 2023, 12:39pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/2 "2023-10-20T12:39:31Z")

</div>

This is something normally done in the database with `lead()` or `lag()` expressions. Show your `Query1` and `Query2` and we can help you do this the "right" way.

---

<div class="post-metadata">

**Author:** ![jgooding](https://avatars.discourse-cdn.com/v4/letter/j/a88e4f/32.png) [@jgooding](https://forum.inductiveautomation.com/u/jgooding)\
**Post date:** [October 20, 2023, 1:51pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/3 "2023-10-20T13:51:51Z")

</div>

First, I agree with Paul this is marginal approach.

You need to pair down your pydataset to just call the column with the timestamps and then it may work. try just using [row[0]] and and not row[0], none. Append by nature just adds it to the row of data.

What are you going to do if for some reason your db misses a start or stop event. Once that happens your dataset will be completely confused and rubbish. It will happen it is just a matter of time.

If you want to use the method that you are currently using, I would recommend you iterate over your list of start times and then add to the stop column the first time in your next list that is greater than your start time. If your dataset is really large this may be extremely inefficient and you may want to consider something else. One easy way is to drop the entries that are all before the start time so the tool only has to look through a smaller list every time.

The sql method is to sort your dataset and filter just the columns with start times and retrieve the next entry where the db says stop. This method of querying is also inefficient (can take a lot of memory and cpu usage) since you are iterating over the entire dataset to come up with your list. Not too bad for small data sets but it could cause performance issues.

I bet you if you post your queries someone on this forum may post the code you need to solve this.

---

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 20, 2023, 2:40pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/4 "2023-10-20T14:40:44Z")

</div>

Hello pturmel and jgooding

Many thanks for prompt response. Below is the table:

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

Below are the two queries:

select ts,start from tbl\_pumpevent\_babu WHERE start IS NOT NULL and DATE\_FORMAT(ts, '%Y%m%d')=DATE\_FORMAT(NOW(), '%Y%m%d')

select ts,stop from tbl\_pumpevent\_babu WHERE stop IS NOT NULL and DATE\_FORMAT(ts, '%Y%m%d')=DATE\_FORMAT(NOW(), '%Y%m%d')

Thanks again for your support.

---

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 20, 2023, 3:47pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/5 "2023-10-20T15:47:48Z")

</div>

Will it be possible to use system.dataset.addColumn() in this case? I am not sure if it is possible to use this function to add column with timestamp values.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 20, 2023, 8:56pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/6 "2023-10-20T20:56:11Z")

</div>

Assuming the table format is guaranteed.

```sql
SELECT started, stopped
FROM (SELECT ts AS started, start, stop, lead(ts, 1, NULL) OVER (ORDER BY ts) AS stopped
      FROM tbl_pumpevent_babu
      ORDER BY ts) subq
WHERE start = 1

```

Consider the table:  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/9/79149da0ed0a61be2e16909398724b367ad2e2c9.png)

The subquery's lead function grabs the time stamp from the following row.

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

Then, the main query filters the rows with the start flag.  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/0/5/052b2be2f330403681a73337ce1f7e3bc942667e.png)

---

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 21, 2023, 3:11pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/7 "2023-10-21T15:11:34Z")

</div>

Hello [JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)

Your solution worked. For some reason, "lead(ts,1,NULL)" is giving me error but "lead(ts)" is working.

The working script is as below:

SELECT started, stopped  
FROM (SELECT ts AS started, start, stop, lead(ts) OVER (ORDER BY ts) AS stopped  
FROM tbl\_pumpevent\_babu  
ORDER BY ts) subq  
WHERE start = 1

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [October 23, 2023, 10:45am UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/8 "2023-10-23T10:45:25Z")

</div>

Different databases, different flavors. Glad you got it working. 🙂

---

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 23, 2023, 11:12am UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/9 "2023-10-23T11:12:04Z")

</div>

Thanks a lot for your support.

---

<div class="post-metadata">

**Author:** ![maqsudbd](https://avatars.discourse-cdn.com/v4/letter/m/74df32/32.png) [@maqsudbd](https://forum.inductiveautomation.com/u/maqsudbd)\
**Post date:** [October 31, 2023, 2:49pm UTC](https://forum.inductiveautomation.com/t/creating-new-dataset-for-ignition-reporting-from-two-sql-queries/80756/10 "2023-10-31T14:49:52Z")

</div>

Hi

I have a subsequent topic related to this topic.

> [@Day wise report from a table of monthly data](https://forum.inductiveautomation.com/t/day-wise-report-from-a-table-of-monthly-data/81179/2):
>
> If you're just looking for number of events and duration for each day, you can select the start timestamp as a DATE() and group the data by that date.

Please have a look if possible.

Thanks.