Post reply

Statistics calculation

  • February 14, 2009 at 12:12 am

    #206518

    Folks,

    I am a DBA in my organization and my client has given us new requirements to basically capture user information such as UserID, his/her emotion and the geo location and then based on these attributes, create a statistics out of this information. The statistics will be predefined in the system.

    This is the exact requirement

    1) Capture the above information entered by the user.

    2) Based on the information entered by the user, calculate the statistics numbers and display the statistics back to the user.

    For example 50 users in CALIFORNIA are right now happy.

    There are predefined set of 14 statistics that the client has defined. But this list could be expanded in the future.

    I am creating a new table to store the information. All the attributes are stored in a setup table and referenced by ID in the new table along with value of the attribute. This is straightforward.

    However i am concerned about the performance of implementing the step 2, basically i am thinking of writing a Stored proc that is going to use Dynamic SQL based on the different attributes.

    Currently there are 14 statistics for which i am thinking of writing 14 different queries.

    To sum up, collect information from users and display statistics back to the users.

    Any suggestions? Is this a correct solution or could be architected in a different way? Thoughts?

    Thanks in advance.

    Amol

    Amol Naik

  • February 16, 2009 at 8:05 am

    #944563

    Could you post your actual tables with some test data and the results you are looking for? It will be easier to make suggestions with this information.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 20, 2009 at 8:46 pm

    #948093

    Simple aggregates with GROUP BY should do the trick.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply