Data from dataset to another dataset

Ignition
1

I have a dataset that uses a SQL query to bring in a running total from a flow meter for every previous day of the month at midnight. Example:

day31 24:00:00 800 gpm
day1 24:00:00 1000 gpm
day2 24:00:00 1500 gpm
day3 24:00:00 2200 gpm
etc…

I would like to take the data in that dataset and create another dataset with the daily flow total from each day, which is achieved by subtracting the previous days running total. Example:

day1 24:00:00 200 gpm (1000 - 800)
day2 24:00:00 500 gpm (1500 - 1000)
day 3 24:00:00 700 gpm (2200 - 1500)

I have talked to tech support and they have recommended scripting to create the dataset I am looking for, but I am not very good at creating new scripts. I can understand and modify an existing script, but do not have the skill level to create one. If someone could point me in the right direction or offer any type of assistance I would appreciate it.

Tech support also recommended scripting in MySQL, but I would like to keep this data in Ignition if I could. Thanks.

2

Doing it in SQL is the easiest solution.

SELECT t.t_stamp,
	t.Totalizer1,
	COALESCE((t.Totalizer1- (SELECT t1.Totalizer1
		FROM MyTable as t1
		WHERE t1.t_stamp = DATEADD(day, -1, t.t_stamp))),0) as Total
	
FROM MyTable as t

EDIT: I changed the AttrValue to Totalizer. Sorry about that.

I added the COALESCE to return ‘0’ if null, you can remove it if you want.
Then I just take today’s value - yesterdays and call it Total.

Hope that helps!

Cheers,
Chris

3

[quote=“CPowell”]Doing it in SQL is the easiest solution.

SELECT t.t_stamp,
	t.Totalizer1,
	COALESCE((t.AttrValue - (SELECT t1.AttrValue
		FROM MyTable as t1
		WHERE t1.t_stamp = DATEADD(day, -1, t.t_stamp))),0) as Total
	
FROM MyTable as t

I added the COALESCE to return ‘0’ if null, you can remove it if you want.
Then I just take today’s value - yesterdays and call it Total.

Hope that helps!

Cheers,
Chris[/quote]

This will take me a little while to wrap my head around (doesn’t help that I am coming down with a cold…). I will mess around with it in a test database. Our dataset is comprised of 159 columns, so I will have to make it work with all of them. Thanks for the assistance!

4

Glad to help. If you need more assistance, just ask…