How to create Quintiles

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
FROM
(
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)

nmcdermaid,

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.

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.

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