Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Calculating active periods

    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)
    OR
    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.

    Many Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't you just select customers who's start date is prior to the target month, and who's end date is either after the target month or NULL?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Sep 2010
    Posts
    2

    Hi

    Hi

    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?

    Thanks
    Richard
    Last edited by greenwoodr; 09-21-10 at 12:20.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Can't you just select customers who's start date is before the end of the CURRENT MONTH, and who's end date is either after the start of the PRIOR MONTH or NULL?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •