# Recording Daily Totals **URL:** \ **Category:** General Discussion\ **Created:** [August 19, 2013, 5:35pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082 "2013-08-19T17:35:23Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [August 19, 2013, 5:35pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/1 "2013-08-19T17:35:23Z")
We often have to record daily totals, be it counters, runtime meters, or in my case I have to log daily flows. The following is an example on how to simplify this process. This example is for MySQL. I’m going to use two tables log\_counter and daily\_counter. The log\_counter table will hold the counters’ total value and the daily\_counter table will hold the counters’ calculated daily total. Note - The counters do not get reset, they are on going totals. Log\_counter table has the following columns - id, record\_date, tag, counter\_value, t\_stamp Daily\_counter table has the following columns - id, record\_date, tag, daily\_counter\_value, t\_stamp The first task is to create a Block Transaction Group called LogCounters to log the counters’ current values into the log\_counter table. Start by creating a Triggered Expression Item called RecordDate use the query “SELECT DATE(NOW())” and set the Target Name to record\_date. Create two Block Items , Tag and CounterValue set their Target Names to tag and counter\_value. Now you add your tagnames and counter values to the Block Items. Finally set your group to execute at a certain time. When the transaction group executes it will insert one row for each counter we have added to the block items. Note - The tag column is a string column, it holds the string name of the tag, it doesn’t have to be an actual tag, it’s just a string value that is used to a name to the counter. Now for the daily total calculations. Create a new Transaction Group called LogDailyCounters, set the group to execute at some time after the LogCounters group executes, and create a Triggered Expression Item called LogDailyCounts with the following query - `INSERT INTO daily_counter (record_date, tag, daily_counter_value, t_stamp) SELECT yesterday.record_date, today.tag, today.counter_value - yesterday.counter_value as daily_counter_value, NOW() FROM log_counter today INNER JOIN log_counter yesterday ON DATE_SUB(today.record_date, INTERVAL 1 DAY) = yesterday.record_date AND today.tag = yesterday.tag WHERE today.record_date = DATE(NOW())` and that’s it 🙂 So what’s the query doing for us? It’s looking at every row in the daily\_counter table where record\_date equals today, finding today’s counter value and subtracting it from yesterday’s counter value and inserting that “daily total” value into the daily\_counter table. How is it doing that? It starts with the FROM and INNER JOIN statement, you’ll notice they are the same tables with different alias names. So for a second pretend you printed out two copies of the log\_counter table, write Today on one copy and Yesterday on the other copy. Next we determine how those two copies are related, that occurs in the ON statement, here we are saying find the record\_date in the Today copy, subtract one day and find that date in the Yesterday copy AND the tag ‘names’ have to be the same, then we have matching rows. The WHERE clause says we are only interested in the records in the Today copy where the record\_date is today. Finally SELECT statement says we want to take the counter\_value from the Today table and subtract it from the matching counter\_value in the Yesterday table. Benefits - Daily calculations are automatic. As long as I set-up a counter to be logged in the LogCounters transaction group, then the query in the LogDailyCounters transaction group will automatically calculate and record the daily total. If I need to add or remove a counter I only have to do it in the LogCounters transaction group, the query in the LogDailyCounters transaction group will take care of the rest. I also have an log of the on going counter totals in the log\_counter table, no counter resets. Can you do total calculations based on different time intervals? Sure can, I used a daily calculation example because it’s easier to follow, but you could use a different time interval if you change the time reference used in the ON clause. ---
**Author:** ![gpg](https://avatars.discourse-cdn.com/v4/letter/g/f19dbf/32.png) [@gpg](https://forum.inductiveautomation.com/u/gpg)\ **Post date:** [February 11, 2014, 1:28am UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/2 "2014-02-11T01:28:45Z")
This looks like a very promising post, But I am not able to get it to work. This is what I have so far Any idea where Im going wrong. I appreciate it Thanks ---
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [February 11, 2014, 5:33pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/3 "2014-02-11T17:33:04Z")
You only had one record in your log\_counter table, so when the daily\_counter query ran it didn’t find a second record to do the daily calculation. Set your LogCounters transaction group to execute at 7:00. Set your LogDailyCounter transaction group to execute at 7:15. Basically what you are doing is logging your counter values in the log\_counter table once a day at the same time. The query for the LogDailyCounter transaction group will look at the log\_counter table for all the records for today, then it will find the matching records from yesterday and insert the difference between the counter values in the daily\_counter table. ---
**Author:** ![RClem](https://avatars.discourse-cdn.com/v4/letter/r/50afbb/32.png) [@RClem](https://forum.inductiveautomation.com/u/RClem)\ **Post date:** [October 17, 2014, 1:40pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/4 "2014-10-17T13:40:46Z")
I have the log\_meter group (aka log\_counter) and associated table working, but the daily total is not calculating and transferring the daily\_gas\_total (aka daily\_counter) table. I have three gas flow meters recording their total at 12:01am and the triggered expression occurring at 12:15am. `INSERT INTO daily_gas_total (record_date, tag, daily_meter_value, t_stamp) SELECT yesterday.record_date, today.tag, today.meter_value - yesterday.meter_value as daily_meter_value, NOW() FROM log_meter today INNER JOIN log_meter yesterday ON DATE_SUB(today.record_date, INTERVAL 1 DAY) = yesterday.record_date AND today.tag = yesterday.tag WHERE today.record_date=DATE(NOW());` log\_meter table: ID, record\_date, tag, meter\_value, t\_stamp 1, 2014-10-15 00:00:02, AHT1, 248569243, 2014-10-16 06:57:54 2, 2014-10-15 00:00:02, AHT2, 150751820, 2014-10-16 06:57:54 3, 2014-10-15 00:00:02, AHT3, 204765365, 2014-10-16 06:57:54 4, 2014-10-16 00:00:02, AHT1, 248983089, 2014-10-16 12:44:26 5, 2014-10-16 00:00:02, AHT2, 151008430, 2014-10-16 12:44:26 6, 2014-10-16 00:00:02, AHT3, 205081471, 2014-10-16 12:44:26 34, 2014-10-17 00:01:02, AHT1, 249391246, 2014-10-17 00:01:02 35, 2014-10-17 00:01:02, AHT2, 151265661, 2014-10-17 00:01:02 36, 2014-10-17 00:01:02, AHT3, 205398003, 2014-10-17 00:01:02 daily\_gas\_total table: ID, record\_date, tag, daily\_meter\_value, t\_stamp ---
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [October 18, 2014, 4:52pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/5 "2014-10-18T16:52:20Z")
Your record\_date has a time value in it, so the WHERE clause won’t find any records. In the WHERE clause DATE(NOW()) will return 2014-10-17 00:00:00, since your record\_date values show 2014-10-17 00:01:02 there’s no match. In your log\_meter transaction group make sure you are using a Triggered Expression Item called RecordDate use the query “SELECT DATE(NOW())” and set the Target Name to record\_date. The SELECT DATE(NOW()) query will make sure that your record\_date values won’t have a time value in them. ---
**Author:** ![RClem](https://avatars.discourse-cdn.com/v4/letter/r/50afbb/32.png) [@RClem](https://forum.inductiveautomation.com/u/RClem)\ **Post date:** [October 20, 2014, 10:57am UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/6 "2014-10-20T10:57:53Z")
Thanks Pat, That seems to do the trick when I ran it in the workbench, now I just have to test it on schedule. Rich ---
**Author:** ![Pat](https://avatars.discourse-cdn.com/v4/letter/p/8491ac/32.png) [@Pat](https://forum.inductiveautomation.com/u/Pat)\ **Post date:** [October 20, 2014, 1:06pm UTC](https://forum.inductiveautomation.com/t/recording-daily-totals/7082/7 "2014-10-20T13:06:50Z")
Glad you got it working.