Hello All,
I am OK with SQL when it comes to the basic’s but not very good when tring to do more complicated querries or data extraction. Any suggestions would be welcomed.
I have attached a word document with the queriies I am trying to combine and also one that gives more returns then I am looking for.
Thanks, Chris
SQL querries question.doc (123 KB)
Your first query works because each of your joins only returns one row. For every row you want to use in the calculations, you will have to do another join. I personally think this gets too complicated after just one or two joins - I would definitely drop into Jython for this kind of query. This would allow you to read in the data you are interested in and loop over the dataset repeatedly if required, checking for whatever conditions you wish.
If this sounds practicable, let us know what end result you are looking for and we will try to help you with the code.
Here is what I need to do.
When a production report is requested the operator selects a date for the start point to retrieve data. The system automatically sets the beginning and end time to 06:00:00 (24 hour period).
eg. 1/21/2013 06:00:00 – 1/22/2013 06:00:00
From the selected date at 06:00:00 (Stamped) extract the ‘RunNumber’, ‘Product’, ‘Bin’, ‘Total number of parts’ produced, until the next “Stamped” or total number of parts produced from “Stamped” to “End” and “Start” to “Stamped
The following Jython code should let you get started:query = """
SELECT * FROM data
WHERE DW_Date>='2013-01-23 06:00:00'
AND DW_Date<='2013-01-24 06:00:00'
"""
data = system.db.runQuery(query)
firstStampedValue = None
endValue = None
for row in data:
if row["Action"] == "Stamped":
if firstStampedValue is None:
firstStampedValue = row["Accum"]
else:
secondStampedValue = row["Accum"]
elif row["Action"] == "End":
endValue = row["Accum"]
elif row["Action"] == "Start":
startValue = row["Accum"]
if endValue is None:
total = secondStampedValue - firstStampedValue
else:
total = (endValue - firstStampedValue) + (secondStampedValue - startValue)
print totalThis is only returning the difference in the ‘Accum’ value, but you should be able to use this to get what you want.
Thanks for the reply. I had not thought of doing it this way.
Unless I am missing something, I am getting the following error on this line
‘total = secondStampedValue - firstStampedValue’
Traceback (innermost last):
File “event:actionPerformed”, line 22, in ?
TypeError: sub nor rsub defined for these operands
Ignition v7.2.7 (b170)
Java: Sun Microsystems Inc. 1.6.0_35
What is the datatype of the ‘Accum’ field? If it is not already a number you will have to do something liketotal = int(secondStampedValue) - int(firstStampedValue)Also check that these fields actually contain valid values.
Your code example works great for what I need. I have made some minor mods to get the data I need. One thing I found is when I have multiple “Stop” and “Starts” the example only gives the first and last. I have created second, third, fourth… variables but the code gets rather large. Is there a way to dynamically do this as in arrays like VB?
If you wanted to read in multiple values in Jython you would use lists e.g.query = """
SELECT * FROM data
WHERE DW_Date>='2013-01-23 06:00:00'
AND DW_Date<='2013-01-24 06:00:00'
"""
data = system.db.runQuery(query)
#Create empty lists.
stampedValues = []
startValues = []
endValues = []
#Write each value to correct list.
for row in data:
if row["Action"] == "Stamped":
stampedValues.append(row["Accum"])
elif row["Action"] == "End":
endValues.append(row["Accum"])
elif row["Action"] == "Start":
startValues.append( row["Accum"])
#Display list values.
print "stampedValues ", stampedValues
print "startValues ", startValues
print "endValues ", endValuesFor your specific case of finding out the total accumulated, you could do this very neatly by subtracting the first value and then add every subsequent value:query = """
SELECT * FROM data
WHERE DW_Date>='2013-01-22 06:00:00'
AND DW_Date<='2013-01-23 06:00:00'
AND Action IN ('Stamped', 'Start', 'End')
ORDER BY DW_Date
"""
data = system.db.runQuery(query)
firstValue = True
for row in data:
if firstValue:
total = -row["Accum"]
firstValue = False
else:
total += row["Accum"]
print totalNote that this will only work in very specific circumstances: only one day of data; first and last records must be marked with ‘Stamped’; only first and last records are marked with ‘Stamped’; matched pairs of ‘Start’ and ‘End’ values.