--===== If the test table already exists, drop it     
IF OBJECT_ID('TempDB..#Sales','U') IS NOT NULL         
DROP TABLE #Sales
IF OBJECT_ID('TempDB..#RunningTotal2007','U') IS NOT NULL     
DROP TABLE #RunningTotal2007
IF OBJECT_ID('TempDB..#RunningTotal2008','U') IS NOT NULL     
DROP TABLE #RunningTotal2008

--Create the temp table
Create Table #Sales
(
	House_ID Int Primary Key Clustered
	,[2007] Decimal(18,2)
	,[2008] Decimal(18,2)
	,[2007 Rank] nvarchar(50)
	,[2008 Rank] nvarchar(50)
)

--Insert our test data (Actual code runs on 15,000 rows +/-)
Insert Into	#Sales
Select
	House_ID
	,[2007]
	,[2008]
	,Cast(Null As nvarchar(50)) As [2007 Rank]
	,Cast(Null As nvarchar(50)) As [2008 Rank]
From
	(
		Select 
			1 As House_ID
			,125 As [2007]
			,569 As [2008]
		Union All
		Select 2,212,924
		Union All
		Select 3,354,862
		Union All
		Select 4,824,724
		Union All
		Select 5,565,628
		Union All
		Select 6,618,1028
		Union All
		Select 7,784,488
		Union All
		Select 8,465,321
		Union All
		Select 9,994,284
		Union All
		Select 10,1016,161
	)A


--declare our dividor
Declare @Dividor As Decimal(18,4)

--get a running total starting at the smallest number and working to the largest
Select
		a.House_ID As House_ID
		,Sum(b.[2007]) As Total
Into 
	#RunningTotal2007
From
	#Sales a
	Cross Join #Sales b
Where
	IsNull(b.[2007],0) <= IsNull(a.[2007],0)
Group By 
	a.House_ID

--We want 3 groups of customers, high, medium or low ones, this could also be an integer value
--1,2,3 and converted on our output to high, medium low when we join up with our demographics.
Set @Dividor = (Select Sum(IsNull([2007],0)) / 3 from #Sales)
update #Sales
Set [2007 Rank] =	Case 
					When isNull(Temp.Total,0) <= @Dividor Then 'Low' 
					When (isNull(Temp.Total,0) > @Dividor) And (isNull(Temp.Total,0) <= @Dividor*2)  then 'Medium'
					When (isNull(Temp.Total,0) > @Dividor * 2) Then 'High'
				End
From
(
	Select
		House_ID
		,Total
	From 
		 #RunningTotal2007
)Temp
Where
	#Sales.house_ID = temp.house_ID

--Calculate a running total for the next year
Select
		a.House_ID As House_ID
		,Sum(b.[2008]) As Total
Into 
	#RunningTotal2008
From
	#Sales a
	Cross Join #Sales b
Where
	IsNull(b.[2008],0) <= IsNull(a.[2008],0)
Group By 
	a.House_ID

--Again apply the same divisor rules
Set @Dividor = (Select Sum(IsNull([2008],0)) / 3 from #Sales)
update #Sales
Set [2008 Rank] =	Case 
					When isNull(Temp.Total,0) <= @Dividor Then 'Low'
					When (isNull(Temp.Total,0) > @Dividor) And (isNull(Temp.Total,0) <= @Dividor*2)  then 'Medium'
					When (isNull(Temp.Total,0) > @Dividor * 2) Then 'High'
				End
From
(
	Select
		House_ID
		,Total
	From 
		 #RunningTotal2008
)Temp
Where
	#Sales.house_ID = temp.house_ID


--Lets have the results
Select
	*	
From
	#Sales
Order By
	[2008] Desc

select House_ID, [2007], Ntile(3) over (order by [2007] desc) as '2007 Ranking', [2008], NTILE(3) over (order by [2008] desc) as '2008 Ranking' 
from #sales

Select 6,1028,'High' union all
Select 2,924,'High' union all
Select 3,862,'Medium' union all
Select 4,724,'Medium' union all
Select 5,628,'Medium' union all
Select 1,569,'Low' union all
Select 7,488,'Low' union all
Select 8,321,'Low' union all
Select 9,284,'Low' union all
Select 10,161,'Low'

Select
	Sum([High]) As High
	,Sum(High)/ (Sum(High) + Sum(Medium) + Sum(Low)) * 100 As [% Of Total High]
	,Sum([Medium]) As Medium
	,Sum(Medium)/ (Sum(High) + Sum(Medium) + Sum(Low)) * 100 As [% Of Total High]
	,Sum([Low]) As Low
	,Sum(Low)/ (Sum(High) + Sum(Medium) + Sum(Low)) * 100 As [% Of Total High]
	,(Sum(High) + Sum(Medium) + Sum(Low)) As [Total]
From
(
Select 6 As house_ID,Cast(1028 As Decimal(18,2)) As [2008],'High' As [Rank] union all
Select 2,924,'High' union all
Select 3,862,'Medium' union all
Select 4,724,'Medium' union all
Select 5,628,'Medium' union all
Select 1,569,'Low' union all
Select 7,488,'Low' union all
Select 8,321,'Low' union all
Select 9,284,'Low' union all
Select 10,161,'Low'
)A
Pivot
(Sum([2008])
For Rank in ([High],[Medium],[Low]))B

--===== If the test table already exists, drop it     
IF OBJECT_ID('TempDB..#Sales','U') IS NOT NULL         
DROP TABLE #Sales
IF OBJECT_ID('TempDB..#RunningTotal2007','U') IS NOT NULL     
DROP TABLE #RunningTotal2007
IF OBJECT_ID('TempDB..#RunningTotal2008','U') IS NOT NULL     
DROP TABLE #RunningTotal2008
GO
--Create the temp table
Create Table #Sales
(
        House_ID Int Primary Key Clustered
        ,[2007] Decimal(18,2)
        ,[2008] Decimal(18,2)
        ,[2007 Rank] nvarchar(50)
        ,[2008 Rank] nvarchar(50)
)

--Insert our test data (Actual code runs on 15,000 rows +/-)
Insert Into        #Sales
Select
        House_ID
        ,[2007]
        ,[2008]
        ,Cast(Null As nvarchar(50)) As [2007 Rank]
        ,Cast(Null As nvarchar(50)) As [2008 Rank]
From
        (
                Select 
                        1 As House_ID
                        ,125 As [2007]
                        ,569 As [2008]
                Union All
                Select 2,212,924
                Union All
                Select 3,354,862
                Union All
                Select 4,824,724
                Union All
                Select 5,565,628
                Union All
                Select 6,618,1028
                Union All
                Select 7,784,488
                Union All
                Select 8,465,321
                Union All
                Select 9,994,284
                Union All
                Select 10,1016,161
        )A


--declare our dividor
Declare @Dividor As Decimal(18,4)

--===== Declare a variable to help with the running total calculations (change)
DECLARE @PrevTotal DECIMAL(9,2)

--get a running total starting at the smallest number and working to the largest
--===== Put the data we need into a temp table... note the additional columns
 SELECT IDENTITY(INT,1,1) AS RowNum,
        a.House_ID As House_ID
        ,CAST(SUM(b.[2007]) AS DECIMAL(9,2)) As HouseTotal
        ,CAST(0 AS DECIMAL(9,2)) AS Total
   INTO #RunningTotal2007
   FROM #Sales a
  INNER JOIN #Sales b ON A.House_ID = B.House_ID
  GROUP BY a.House_ID
  ORDER BY SUM(b.[2007]) ASC

--===== Throw a clustered key on it to keep things in order during the UPDATE
  ALTER TABLE #RunningTotal2007 ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Reset the variable we need to do the running total
    SET @PrevTotal = 0

--===== Do the running total as lightning speed
 UPDATE #RunningTotal2007
    SET @PrevTotal = Total = HouseTotal+@PrevTotal
   FROM #RunningTotal2007 WITH(INDEX(0)) --Forces clustered index scan

--===== End of changes


--We want 3 groups of customers, high, medium or low ones, this could also be an integer value
--1,2,3 and converted on our output to high, medium low when we join up with our demographics.
Set @Dividor = (Select Sum(IsNull([2007],0)) / 3 from #Sales)
update #Sales
Set [2007 Rank] =        Case 
                                        When isNull(Temp.Total,0) <= @Dividor Then 'Low' 
                                        When (isNull(Temp.Total,0) > @Dividor) And (isNull(Temp.Total,0) <= @Dividor*2)  then 'Medium'
                                        When (isNull(Temp.Total,0) > @Dividor * 2) Then 'High'
                                End
From
(
        Select
                House_ID
                ,Total
        From 
                 #RunningTotal2007
)Temp
Where
        #Sales.house_ID = temp.house_ID

--Calculate a running total for the next year (Change, whole section replaced)
--===== Put the data we need into a temp table... note the additional columns
 SELECT IDENTITY(INT,1,1) AS RowNum,
        a.House_ID As House_ID
        ,CAST(SUM(b.[2008]) AS DECIMAL(9,2)) As HouseTotal
        ,CAST(0 AS DECIMAL(9,2)) AS Total
   INTO #RunningTotal2008
   FROM #Sales a
  INNER JOIN #Sales b ON A.House_ID = B.House_ID
  GROUP BY a.House_ID
  ORDER BY SUM(b.[2008]) ASC

--===== Throw a clustered key on it to keep things in order during the UPDATE
  ALTER TABLE #RunningTotal2008 ADD PRIMARY KEY CLUSTERED (RowNum)

--===== Reset the variable we need to do the running total
    SET @PrevTotal = 0

--===== Do the running total as lightning speed
 UPDATE #RunningTotal2008
    SET @PrevTotal = Total = HouseTotal+@PrevTotal
   FROM #RunningTotal2008 WITH(INDEX(0)) --Forces clustered index scan

--===== End of changes

--Again apply the same divisor rules
Set @Dividor = (Select Sum(IsNull([2008],0)) / 3 from #Sales)
update #Sales
Set [2008 Rank] =        Case 
                                        When isNull(Temp.Total,0) <= @Dividor Then 'Low'
                                        When (isNull(Temp.Total,0) > @Dividor) And (isNull(Temp.Total,0) <= @Dividor*2)  then 'Medium'
                                        When (isNull(Temp.Total,0) > @Dividor * 2) Then 'High'
                                End
From
(
        Select
                House_ID
                ,Total
        From 
                 #RunningTotal2008
)Temp
Where
        #Sales.house_ID = temp.house_ID


--Lets have the results
Select
        *        
From
        #Sales
Order By
        [2008] Desc