It is currently Fri Jul 25, 2014 2:37 am




Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 24 posts ]  Go to page 1, 2  Next
 Thinking about sumations 
Author Message
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Thinking about sumations
I've got my RUNNING tag in my UDT set up with history enabled.. 1 minute scan class. I'm looking at creating a tag within the UDT that can sum the historical values of this (a bool value tag) so that i could return a value that keeps track of how many minutes a motor has been on.

Image

Should I be doing this directly using a query? and if so, what kind of coding are we dealing with?


Fri Jun 22, 2012 11:30 am
Profile
Moderator
Moderator

Joined: Fri Dec 04, 2009 9:02 am
Posts: 177
Post Re: Thinking about sumations
You won't want to query the database directly. Instead, use a system.tag.queryTagHistory() inside of an expression with the runScript() expression function.


Fri Jun 22, 2012 2:47 pm
Profile
Moderator
Moderator

Joined: Fri Mar 16, 2012 1:00 pm
Posts: 894
Location: Sacramento, CA
Post Re: Thinking about sumations
I was able to do this with a memory tag and a client tag. The memory tag would be the boolean for the motor on/off, the client tag would be your runtime. And this is the expression that I put on the client tag:

Code:
if({Memtag}, dateDiff(toDate({Memtag.LastChange}), toDate(now()), "minute"), 0)

_________________
Greg Simpson
Inductive Automation
Technical Support Rep


Fri Jun 22, 2012 3:00 pm
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
I think I may be better off using a historical transaction group in this case and executing once a minute and then inside of my window, I can indirectly reference my tag with the appropriate column in my database and just query it directly with a sum.

Historically trending for my tags isn't really necessary in this case although the redundancy wouldn't kill my project with latency issues, would it? Also, I would rather log it all into my db so if a random person wants to look at the data directly, it's easier to view it.

Can I add db columns on the fly for new group items or do I have to redesign my db table to take the new column and then assign a designation?
I may have over 200 motors I want to track history for every minute and if I have to add or remove some, I'd like to balance flexibility with simplicity.
I'm logging the data now but I'm not sure if I am organizing my db in the most efficient way to make room for changes to the transaction group.
Would it be crazy to make 200 different tables and transaction groups just so I won't have to edit db table columns?
Any thoughts on this?


Mon Jun 25, 2012 7:01 am
Profile
Moderator
Moderator

Joined: Fri Mar 16, 2012 1:00 pm
Posts: 894
Location: Sacramento, CA
Post Re: Thinking about sumations
This sounds like an entirely different question from the original post.

_________________
Greg Simpson
Inductive Automation
Technical Support Rep


Mon Jun 25, 2012 7:13 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
Greg.Simpson wrote:
This sounds like an entirely different question from the original post.


I'm just trying to get an idea of how someone else might set this up.. I am doing it now completely different then I thought I was going to, yes.


Mon Jun 25, 2012 7:21 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2462
Post Re: Thinking about sumations
Hi,

I think you're right that transaction groups would be the easiest way to go. We need to beef up our tag history query capabilities over time, and SUM definitely needs to be added soon.

With groups, you have a few options:
1) You can do exactly what you had in mind: log every second, query with SUM(), maybe set the group(s) to delete after 1 month or something.
2) Items in groups can actually be "Hour Meters", which are pretty much what you want. You take your on/off tag, set its data type to float (or int), and set it to be an hourmeter "on 1". While the tag is 1, it will accumulate value (the group can run at any rate). When it goes to 0, it will reset or not, depending on whether it's "retentive". With 7.5, there's an additional option for when to reset the value. So, instead of logging history and calculating a sum, you could have hourmeters set up to record to the last row of a table. If you want to really get tricky, have the group set up as "bi-dir, db wins". Then, you can just insert a new row with 0s in the columns to reset the values and start over (for shifts, or per day, for example).

Now, for your group questions:
- Overhead for logging probably won't affect your device communication much. You already have the items subscribed elsewhere, so it won't be a big deal.
- You can definitely have 200 groups, though I prefer to minimize the number of groups when possible, as it can get a tad cumbersome to work with so many. But, 200 isn't too bad, 1000 is rough. Still, if you have 200 motors, it would probably just be better to make a "motor_runtime" group with 200 columns.
- You can definitely just add new items to a group. It will just add the columns onto the table for you.

Hope this helps,

_________________
Colby Clegg
Inductive Automation
Software Development


Mon Jun 25, 2012 8:10 am
Profile
General
General

Joined: Tue Oct 26, 2010 5:58 pm
Posts: 152
Post Re: Thinking about sumations
Alternative approach.

I presume you are using a PLC to derive your running status

Rather than datalogging every minute while you are running, how about just log run events (or down time events)? i.e. timer in the plc keeps track of up time (or downtime) in minutes. When a running or downtime event finishes, the plc sets a boolean tag to true, loads the downtime count into a tag and a transaction group logs the minute count (and a time stamp) into a database.

We have done it this way for downtime tracking on some of our attached plant.

You can then sum minute counts between different dates and times to get downtime in a day, downtime in a month etc, as well as being able to allocate reasons for downtime and some other follow on stuff relatively easily. It becomes an easy matter to build reports, charts etc off this.

Note that we are not using the downtime and OEE module.

Just throwing it out there. Good luck! :thumb_left:


Mon Jun 25, 2012 9:53 am
Profile
General
General

Joined: Wed Aug 08, 2007 5:05 am
Posts: 218
Post Re: Thinking about sumations
Use a Block Transaction Group and have Ignition do the timing calculation for you, no mess no fuss. As Colby mentioned you can set up a transaction item to be an “Hour Meter”, so you could create a Standard Group with 200 items that correspond to 200 columns in some table, this is probably easiest for most users. But, as Colby also mentioned, it can be cumbersome working with a table with so many columns, plus it’s not good database design. It’s better to have a long table instead of a wide table and there are two ways to accomplish that in this case, 1) create a Standard Group for each of your motors or 2) use one Block Group for all your motors.

A simple table would be something like motor_id, timer, counter, t_stamp (although you didn’t mention anything about having a counter, it’s simple to add and it’s valuable information). From there you could set-up a Standard Group for each of your motors, where each of the groups would have the following items - a motor id item, a running status item set it to be a timer, and a second running status item set it to be a counter. The drawback with doing it this way, it can be a little hectic to manage and it’ll take some time to implement. Enter the Block Group, create one block group, add three block items MotorID, Timer, Counter, and add your tags to the block items. About the hardest thing about using the block group is making sure all your items are in the same row, but the Block View tab allows you to easily view the data. It’s easy to manage, clean, and probably has a faster execution time than using multiple standard groups. Block Groups are great for doing historical logging as well.


Tue Jun 26, 2012 5:42 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
Colby.Clegg wrote:
If you want to really get tricky, have the group set up as "bi-dir, db wins". Then, you can just insert a new row with 0s in the columns to reset the values and start over (for shifts, or per day, for example).


I would like to do this.
I am updating the last row in my group.
How can I insert a new row without doing an insert into. ... and naming all of my columns?


Tue Jun 26, 2012 6:43 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2462
Post Re: Thinking about sumations
Different databases will behave a little differently (and it probably depends on the table setup a bit), but in general there's no rule about having to specify each column. For example, in mysql you could do something as simple as:
Code:
INSERT INTO yourtable() values()

to just use the default values for all columns.

Regards,

_________________
Colby Clegg
Inductive Automation
Software Development


Tue Jun 26, 2012 7:43 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
anyone know how to do this with MS SQL server?
So far, I can't find anywhere where it even says it is possible to do this


Tue Jun 26, 2012 10:00 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 3:43 pm
Posts: 2462
Post Re: Thinking about sumations
Well, according to the documentation for INSERT for SQL Server, it looks like you should be allowed to do:
Code:
INSERT INTO yourtable DEFAULT VALUES


If nothing else, you should also be able to insert with just the timestamp:
Code:
INSERT INTO yourtable(t_stamp) VALUES(CURRENT_TIMESTAMP)


Regards,

_________________
Colby Clegg
Inductive Automation
Software Development


Tue Jun 26, 2012 10:28 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
using the timestamp as a placeholder worked for me..
Thanks a bunch.. SQL is very new to me.
Where might I use this SQL code lets say if I wanted to push a button to call the insert query.


Tue Jun 26, 2012 11:08 am
Profile
General
General

Joined: Wed Apr 25, 2012 5:51 am
Posts: 387
Location: Terryville, CT
Post Re: Thinking about sumations
On button action I am writing to a tag called shiftreset a value of 1.
Inside my group, my items are set to reset when this tag is = to 1
It looks like it is working how I had wanted it to work, resetting when I click the button and creating a new row in my table.
However, I never reset the tag to 0.... how is it not resetting my hour meter every second when the tag value is always a 1?

Image


Looking a little further down the line here,
I would like to pick the last item in one of these columns in my table out and display it in a window.
I would like to indirectly reference which motor I need to value for as well..
Where could I implement code and how?
I have something like this now:

Label Text binded to:

DECLARE @MyVar VARCHAR(50) = 'dbo.motorrun.' + '{Root Container.MotorID}'
select (@MyVar) from dbo.motorrun
where last(timestamp)

This won't work.. Even if I take out the Where, it only displays dbo.motorrun.MOTOR 101 instead of a value in the column


Wed Jun 27, 2012 6:51 am
Profile
Display posts from previous:  Sort by  
Post new topic This topic is locked, you cannot edit posts or make further replies.  [ 24 posts ]  Go to page 1, 2  Next


Who is online

Users browsing this forum: alcaan, Google [Bot] and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: