MSSQL Query Help

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?

How is the data stored currently?

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

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

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.

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