I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So I need to convert the date to a format of yyyymm and count all order from selected month and sum up all orders that where made until 12 months back.
The outcome should look like:
I started with this query but it only counts orders for the selected month and not includes orders for former months.
SELECT TO_CHAR(create_date,'yyyymm') AS yyyymm, COUNT(order_id ) AS Number_of_orders_12_month_period FROM Orders WHERE TO_CHAR(create_date,'yyyymm') BETWEEN DATEADD(month,-12,create_date) AND TO_CHAR(create_date,'yyyymm') group by 1