# MSSQL Query Help

**URL:** <https://forum.inductiveautomation.com/t/mssql-query-help/42005>\
**Category:** General Discussion\
**Created:** [January 6, 2021, 7:08pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005 "2021-01-06T19:08:07Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [January 6, 2021, 7:08pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/1 "2021-01-06T19:08:07Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 7, 2021, 12:43pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/2 "2021-01-07T12:43:49Z")

</div>

How is the data stored currently?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 7, 2021, 1:05pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/3 "2021-01-07T13:05:14Z")

</div>

It will be something of the form

```auto
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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 7, 2021, 2:55pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/4 "2021-01-07T14:55:42Z")

</div>

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()

---

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [January 7, 2021, 5:43pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/5 "2021-01-07T17:43:00Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [January 16, 2021, 7:53am UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/6 "2021-01-16T07:53:56Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 16, 2021, 10:59am UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/7 "2021-01-16T10:59:02Z")

</div>

It could look something like this:

```python
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)

```

---

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [January 16, 2021, 11:32am UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/8 "2021-01-16T11:32:12Z")

</div>

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”

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 16, 2021, 12:20pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/9 "2021-01-16T12:20:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\
**Post date:** [January 16, 2021, 12:28pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/10 "2021-01-16T12:28:20Z")

</div>

DOH! Try aliasing the Max aggregate.

```sql
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

```python
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')

```

---

<div class="post-metadata">

**Author:** ![vasilevdim](https://avatars.discourse-cdn.com/v4/letter/v/7ba0ec/32.png) [@vasilevdim](https://forum.inductiveautomation.com/u/vasilevdim)\
**Post date:** [January 16, 2021, 1:46pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/11 "2021-01-16T13:46:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [November 8, 2023, 8:59am UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/12 "2023-11-08T08:59:20Z")

</div>

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)

```auto
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

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 8, 2023, 9:11am UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/13 "2023-11-08T09:11:58Z")

</div>

What's the relation between your query and the spreadsheet you posted ?  
What does the "MES\_Lite\_Full\_Machines\_Delta" table look like ?

---

<div class="post-metadata">

**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\
**Post date:** [November 8, 2023, 3:18pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/15 "2023-11-08T15:18:00Z")

</div>

> [@kavya\_shree](#):
>
> I copied my table data in spreadsheet. you can see columns

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

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [November 8, 2023, 5:23pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/16 "2023-11-08T17:23:58Z")

</div>

very sorry I have attached different table. Please refer this.  
[MES\_table.xlsx](https://forum.inductiveautomation.com/uploads/short-url/9ncZM2XC1Hr3pkVQa8ncl5zHHFK.xlsx) (1.1 MB)

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [November 8, 2023, 5:42pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/17 "2023-11-08T17:42:25Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [November 9, 2023, 2:06pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/19 "2023-11-09T14:06:17Z")

</div>

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_

---

<div class="post-metadata">

**Author:** ![Tim\_Carlton](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/tim_carlton/32/52835_2.png) [@Tim\_Carlton](https://forum.inductiveautomation.com/u/Tim_Carlton)\
**Post date:** [November 9, 2023, 2:10pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/20 "2023-11-09T14:10:53Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![kavya\_shree](https://avatars.discourse-cdn.com/v4/letter/k/b4bc9f/32.png) [@kavya\_shree](https://forum.inductiveautomation.com/u/kavya_shree)\
**Post date:** [November 9, 2023, 2:17pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/21 "2023-11-09T14:17:19Z")

</div>

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.

```auto
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

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [November 9, 2023, 5:30pm UTC](https://forum.inductiveautomation.com/t/mssql-query-help/42005/22 "2023-11-09T17:30:41Z")

</div>

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

```auto
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

```