Calculating amount from transactions...

[cristianivanoff]

Hi All,
Dont know if this is in the correct category but I'm trying to calculate amounts in sql.
This what I have:

One table with transactions. That's it basically. Every transaction represents an "deposit" or "withdrawal" of a product from a shelf. There's no data of the amount of products in the shelf and now I would like to calculate the amount of products in all shelfs during the day. Every day.

Table:
Transaction Datetime, Source, Destination, Product ID, Product Group
2019-02-01 08:01:00, Person1, Shelf1, 1234, 1
2019-02-01 10:01:00, Shelf1, Person1, 1234, 1
2019-02-01 08:03:00, Person2, Shelf1, 5678, 1
...

Desired table:
Hour, Date, Shelf, Product Group, Amount
8, 2019-02-01, Shelf1, 1, 5
9, 2019-02-01, Shelf1, 1, 10
10, 2019-02-01, Shelf1, 1, 10

Any ideas how to do this? Any suggestion would be appreciated
Br
Cris

 

[fredericofonseca]

that is a standard basic "group by" with a sum of the amount (or sum of 1 if no amount column available)

give it a try and come back with ddl for the table and your code attempts and issues if you need further help - and if you do get back do state sql vendor and version.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886