Hi!
I have three dimensions that are linked together in the following way:
store ---many to one--> company ---many to one--> chain
All three dimensions have their own approval date, create date and some other dates.
I want to create a table showing number of stores, companies, and chains approved or created during the selected time period (see table).
Number of created/approved stores, companies, and chains during Januari and February 2011
| Country | store | company
| chain
|
|---|
| Sweden | 12 | 4 | 1 |
| Norway | 15 | 3 | 0 |
| Finland | 3 | 0 | 0 |
| Denmark | 5 | 0 | 0 |
I only want a company and a chain popping up in the table if the approval/create date of tha comapny or chain was during the selected time period and not all companies and chains connected to the new stores (as I get now). I.e. I get too many companies and chains for every time period (if I don't choose the entire period). This is because the dimensions are linked together.
My script looks something like this:
store:
store_id
company_id
chain_id
store_approval_date
store_create_date
...some more date and other attributes
company:
company_id
company_approval_date
company_create_date
...some more date and other attributes
chain:
chain_id
chain_approval_date
chain_create_date
...some more date and other attributes
I also want to do the same tables for create date and some other dates connected to the dimensions. I do not have a master calender (since I didn't get that to work without synthetic keys) so I use the store_approval_date as the date that can be selected in the application.
Does anyone have a solution on this?
Regards
Anna
