I'm working on a query which will require getting data from two tables and display.
two tables are Products and ProductSales. their common field is productID, and a product can have many productsales values.
structure of ProductSales table is something like this
ProductID Period Sales
PHHDAPDXXXX 154 1
PHHDAPDXXXX 155 1
PHHDAPDXXXX 156 10
Period are stored in a table called Periods
my query will want to know the most recent 12 months sales of this product counting from currenty period, so eventually the query data should display :
ProductID monthq0 monthq1 ... monthq9 monthq10 monthq11 monthq12
PHHDAPDXXXX 0 0 5 4 -46 0
how do i achieve this?