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.