MSSQL Query Help

General Discussion
1

I need help with query that totalizes production counter of machine at the end of every month (i.e. calculating the sum of the daily outputs for all the days at the end of the month) .
Any idea?

2

How is the data stored currently?

3

It will be something of the form

SELECT SUM(valueColumn)
FROM yourTable
GROUP BY YEAR(dateColumn), MONTH(dateColumn)

This will give you the total of your value column for each month. Note the grouping by Year as well, if you didn’t do this, once you were into second year of production then January 2020 would be added to January 2021 totals.

2 Likes
4

The reason I asked is because we store data for every part produced and not as a counter. Therefore we use COUNT() instead of SUM()

2 Likes
5

Hi guys,

Currently there are 20 transaction groups (and the relevant MSSQL tables), collecting data from 20 machines. One of the columns is “DailyOutput” ( which actually is memory tag, whose values are written by gateway script). So at the end of every month it will be very useful to sum the output of every machine from every single day and get the monthly output of each machine.

6

If I have let’s say 10 queries (10 machines), what script can be use to combine all of them in one dataset?

7

It could look something like this:

tableList = ['table1',
             'table2',
			 'table3',
			 'table4',
			 'table5',
			 'table6',
			 'table7',
			 'table8',
			 'table9',
			 'table10'
			]

query = """
        SELECT SUM(valueColumn)
        FROM %s
        GROUP BY YEAR(dateColumn), MONTH(dateColumn)
		"""

headers = []
data = []

for table in tableList:
	# run a query
	result = system.db.runQuery(query % table, 'dbConnectionName')
	# if headers is empty, popuplate it with column names from the query
	if headers = []:
		headers = result.getColumnNames()
	# Add rows to the data
	data.extend([list(row) for row in result])
		
datasetOut = system.dataset.toDataSet(headers, data)
8

Sorry to say I have placed my question not in the correct post.
Actually I need to create data key for report, combining query data sources for 10 machines, and every query is:
SELECT LOT, MAX(Counter) FROM Machine1
GROUP BY LOT
ORDER BY LOT

If I start my script like this:
Machine1Dataset = system.dataset.toPyDataSet(data[‘Machine1’]), trying to follow the pattern from the post “Combine result of two data sources into a table report”, I get the following error:
“1 st arg can’t be coerced to com.inductiveautomation.ignition.common.Dataset”

9

Without seeing what you have, it’s a bit more difficult. Possibly a spelling of ‘Machine1’ or a whitespace at the beginning or end ( i.g. 'Machine1 ') for the datasource name.

10

DOH! Try aliasing the Max aggregate.

SELECT LOT, MAX(Counter) AS QTY FROM Machine1
GROUP BY LOT
ORDER BY LOT

If you are not using the query data sources anywhere else, it would be more efficient to do it in a script. This example uses UNION ALL to combine everything into one query

tableList = ['Machine1',
             'Machine2',
			 'Machine3',
			 'Machine4',
			 'Machine5',
			 'Machine6',
			 'Machine7',
			 'Machine8',
			 'Machine9',
			 'Machine10'
			]

baseQuery = "SELECT LOT, MAX(Counter) AS QTY FROM %s GROUP BY LOT ORDER BY LOT"

query = ' UNION ALL '.join([baseQuery % table for table in tableList])

data['lotCounts'] = system.dataset.toDataSet(system.db.runQuery(query), 'dbConnectionName')
1 Like
11

Thank you, Jordan, I will be able to try and revert on Monday.

12

Hi all,

I am trying for a query to find the total number of pieces produced for last 30 minutes, 60 mins for a particular machine.

I need to find the total Piece(col name: Piece_Count_Perpetual) produced in last 30 minutes for particular machine( col name :Machine_name)

select Machine_Name,
  DATEPART(HOUR, t_stamp) AS HourOfDay,
SUM(Piece_Count_Perpetual)  as sum from
 [MES_LITE].[MES_Lite_Full_Machines_Delta]
where t_stamp >= getdate()
and t_stamp < DateADD(MI, -30, getdate()) and Machine_Name='14001_L1_NIS_FACE_CUP' 
 GROUP BY
        Machine_Name,
		DATEPART(HOUR, t_stamp)

this is my query, please guide

13

What's the relation between your query and the spreadsheet you posted ?
What does the "MES_Lite_Full_Machines_Delta" table look like ?

15

Doesn't look right to me. None of the columns you're referencing in the query appear in the spreadsheet.

1 Like
16

very sorry I have attached different table. Please refer this.
MES_table.xlsx (1.1 MB)

17

Do you want to group by 30 minute chunks and show multiple chunks?
Or do you want to show one result - the totals for the most recent 30 minutes?

19

You don't seem to need the HourOfDay - that will return two records about half the time but you only ever want one.

Your date comparison seems to be backwards - it isn't possible that any t_stamp would be after Now and also before thirty minutes ago

20

Also, this is a new question. In the future you can start a new thread with a question. If an old (already solved) question seems relevant then you can include a reference to it.

1 Like
21

Hi,
Thanks , ok I will make sure for new threads posting. First time I am posting for query help.

I got the solution wrt to my table, here is the solution.

select Machine_Name,sum(Piece_Count_Perpetual) as yield, sum(FLOOR(Expected_Yield)) as standardYield, sum(Scrap) as Scrap from table_name  where Machine_Name='25006_L2_CIM_RACE_CONE' and 
t_stamp >=  DATEADD(MI, -30, GETDATE()) and
 t_stamp <GETDATE() and Expected_Yield >0
group by Machine_Name

This may help for others.

Thank you

2 Likes
22

And nicely formatting the query will help the next guy to work on your code - even if it's you!

select 
    Machine_Name,sum(Piece_Count_Perpetual) as yield, 
    sum(FLOOR(Expected_Yield)) as standardYield, 
    sum(Scrap) as Scrap 
from table_name  
where 
    Machine_Name = '25006_L2_CIM_RACE_CONE' 
  and 
    t_stamp >=  DATEADD(MI, -30, GETDATE()) 
  and
     t_stamp <GETDATE() and Expected_Yield > 0
group by Machine_Name
4 Likes