4 Replies Latest reply: Jun 13, 2011 7:59 AM by Anna Max RSS

    Show data linked to a date dependent on selected date

      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

       

      Countrystorecompany
      chain
      Sweden1241
      Norway1530
      Finland300
      Denmark500

       

      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