Hello guys (and girls)!
As usually, I hope very much on your bright ideas.
I have the following daty in database:
Row.nr. Device nr. Transaction value Transaction Type Transaction number Date
1 K300 -1 Warehouse 1 13.06.2017
2 K300 +1 Client 1 13.06.2017
3 L200 -1 Warehouse 1 15.06.2017
4 L200 +1 Client 1 15.06.2017
5 L200 +1 Warehouse 2 19.06.2017
6 L200 -1 Client 2 19.06.2017
7 L200 -1 Warehouse 3 20.08.2017
8 L200 +1 Client 3 20.08.2017
9 L200 +1 Warehouse 4 21.09.2017
10 L200 -1 Client 4 21.09.2017
etc.
I need get the following information in my SQL and LOAD queries:
For all month (in each year) I need to calculate a number of devices in Clients.
Each transaction to client is +1, from client -1.
Actually, I have to find the last transaction (based on transaction number) for each device (device nr.) within current and previous months and take only positive transactions to clients (Transaction value = +1 AND Transaction Type = Client).
For example, for the monht 06.2017, there would be value of 1 (row 2 only will be selected from K300, but lust transaction for L200 is negative).
My main problem is the way, how to aggragate sums for each month within SQL.
I hope for you help!
Thank you in advance!
