How to create Quintiles

Posted on 2007-10-11
Last Modified: 2007-10-24
My user wants me to create a report that lists various income statement averages for a given shop

They also want to add a colum in the report that lists the average of the income statement averages for all shops that are in the same sales quintile.

I'm thinking I need a way to indicate which sales (4499_TotalRev)  quintile each of the shops is in so I can retrieve the shops in the correct quintile.  (Perhaps add a field called quintile and somehow update it)

here is a small sample of the data in the table named incomeStatement
9901_Shop            4499_TotalRev    4999_TotalGrossMargin    5200_GrossMarginNetPayroll
4166            362872            34            22
1110            898000            12            33
1115            645673            56            44
1117            1050815            12            10
1127            591677            88            15

I'm thinking I have to determine what the quintiles are and then find a way to indicate which shop is in which quintile but not sure the best way to do this.

Any help would be greatly appreciated
Question by:johnnyg123
    LVL 30

    Expert Comment

    Yep, you're exactly. right - the record needs to know what quintile its in before you can find out what percentage it is.

    What options fdo you have? I take it you can add fields.

    How often is the report run? hourly/monthly?

    Will the quintile always be evaluated over the same period, or does this table not contain periods? Is this the shop master table?

    A long term enterpise solution is to create a data warehouse.

    A short term solution is to create a quintile field (as you surmised) and populate it.

    If the guy is talking about quintiles then he'll probably want a rank too at some stage. So I suggest you create a rank field and populate it. Then use the Rank field to evaluate the quintile.

    To populate a rank field called ShopRank in this table, assuming that 9901_Shop is a unique field indicating the shop, use the following:

    CREATE TABLE #ShopRank (9901_Shop INT, 4499_TotalRev DECIMAL(19,2), ShopRank INT Identity (1,1))

    INSERT INTO #ShopRank (9901_Shop, 4499_TotalRev)
    SELECT 9901_Shop, 4499_TotalRev
    FROM incomeStatement
    ORDER BY 4499_TotalRev DESC

    UPDATE incomeStatement
    SET ShopRank = #ShopRank .ShopRank
    FROM #ShopRank
    WHERE  incomeStatement.9901_Shop = #ShopRank.9901_Shop

    DROP TABLE #ShopRank

    Then you can simply assign the quintile (or any kind of ranked banding) as follows:

    UPDATE incomeStatement
    SET QuintileMember = (ShopRank + 1) / (SELECT MAX(ShopRank) / 5 FROM incomeStatement)

    Then you can assign the quintile total (the total for each quintile, assigned to each record) like this:

    UPDATE incomeStatement
    SET QuintileTotal = QuintileRevenue
    SELECT QuintileMember, SUM(4499_TotalRev) As QuintileRevenue
    FROM incomeStatement
    GROUP BY QuintileMember
    ) A
    WHERE incomeStatement.QuintileMember = A.QuintileMember

    Then you can assign the quintile percentage like this:

    UPDATE incomeStatement
    SET QuintilePercentage = QuintileRevenue / 4499_TotalRev * 100

    Note that this requires the addition of these fields:

    ShopRank (INT)
    QuintileMember (TINYINT)
    QuintileTotal (DECIMAL(19,2)
    QuintilePercentage (DECIMAL(9,2)


    Author Comment


    Thanks so much for the detailed reply!

    You raise some good questions/points that I didn't address in my original post.

    Here is some additional info::

     I do have the option to add fields.

    The report is run on demand. (potentially the same shop for different reporting periods)

    The quintile will be evaluated over multiple periods as the table does indeed not contain periods.

    I realize there is a bit more complexity as this table will contain multiple income statemens for the same shop for different reporting periods.


    Author Comment

    I accidently posted my response before I realized that I wrote

    The quintile will be evaluated over multiple periods as the table does indeed not contain periods.

    when I meant

    The quintile will be evaluated over multiple periods as the table does indeed contain periods.

    LVL 30

    Accepted Solution

    So, some questions:

    1. So that table basically contains many records for a given shop, 1 record per period per shop?
    2. Are you ever going to want to see a quintile report as of 6 months ago?

    If the answer is 1.Yes, 2.No, then you need to put your quintile figures in the master shop record, rather than the transactional one.

    So when the quintile is evaluated, is it going to be over, say, a 3 month and a 6 month period from today?


    Author Comment

    the table could contain many records for a shop.

    We have a web form that allows shops to enter income statement values for a given period.

    They can do it on an annual basis or for a given month.  In either case they can not do data entry for an overlapping period.  For example,  if an income statement was entered for the period of january 2006 and december 2006, another entry can not be made for this same period or for any period that would overlap this period.  For example may 2006 to april 2007.

    The shops have an option to see a report from the data entered for any of the periods for which they have entered data.  The report slices and dices data in different ways.  The quintiles would be for the periods entered

    Hope that helps answer  your questions

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    The steps for moving the system databases to a new location are documented in the following technical article: However sometimes after the moving process is finished, though SQL i…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now