Sorry for what may seem an easy question - I am new to SQL and am still finding my way round.
We have data that reflects when a customer was active (as in subscribes to a service) so for each fact in my database i have the following information in our fact table
Unique ID customer ID Start_date End_Date (If a customer is still active the end_date is blank)
I would like to generate a report which counts the total number of active customers in any given month and would like to know the best way to go about this. As far as I can see my options are
1) put in a line in the fact table for each month the subscription is active (which seems a little messy)
2) calculate somewhere (in the database or in the reports) at run time which months the subsription was active to allow them to be added up.
I would like to get to a report as follows
Current month -12 ...........Current Month -1 current month
Number of cust. X ............ Y Z
Do anyone have any suggestions about how I might approach this.
Thats what I would like to do and I can create a query for selecting the current months data but am unsure how do get the previous months data into the same report - how do I set multiple target months for the same report?