Recording Daily Totals

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 :slight_smile:

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.

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

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.

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

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.

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

Glad you got it working.