How to create Quintiles

Posted on 2007-10-11
Medium Priority
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
  • 3
  • 2
LVL 30

Expert Comment

ID: 20062281
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


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

ID: 20065249

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

ID: 20065265
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

nmcdermaid earned 2000 total points
ID: 20073398
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

ID: 20078817
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

862 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