Ranking on several columns

Hi there SQL Gurus :)

please look here: http://web-services.org/compdlrm.html

I need to create a report that looks like that.

I am starting out with creating a temp table and populating the fields I need (this is all working fine, by the way):
CREATE TABLE #tblComp (
      iDealerID int NOT NULL ,
      ProductionUnits int NOT NULL ,
      Finance decimal(18, 8) NOT NULL ,
      FinanceRank int NOT NULL ,
      Life decimal(18, 8) NOT NULL ,
      LifeRank int NOT NULL ,
      Disab decimal(18, 8) NOT NULL ,
      DisabRank int NOT NULL ,
      Warr decimal(18, 8) NOT NULL ,
      WarrRank int NOT NULL ,
      Chem_2000 decimal(18, 8) NOT NULL ,
      Chem_2000Rank int NOT NULL ,
      Etch decimal(18, 8) NOT NULL ,
      EtchRank int NOT NULL ,
      Gross decimal(18, 8) NOT NULL ,
      GrossRank int NOT NULL ,
      Points int NOT NULL
)

INSERT INTO #tblComp (
      iDealerID, ProductionUnits, Finance, FinanceRank, Life, LifeRank,
      Disab, DisabRank, Warr, WarrRank, Chem_2000, Chem_2000Rank,
      Etch, EtchRank, Gross, GrossRank, Points)
SELECT    
      iDealerID,
      sum(ProductionUnits) AS ProductionUnits,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(ContractFinanceTricor + ContractFinanceOther)) AS Finance, 0,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_CreditLifeIns)) AS Life, 0,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_DisabilityIns)) AS Disab, 0,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_WarrTricare)) +
            dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_WarrFactory)) +
            dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_WarrOther)) AS Warr, 0,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_Module2000Pkg)) +
            dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_ChemicalRustPkg)) AS Chem_2000, 0,
      dbo.fn_GetPercent(sum(ProductionUnits), sum(Contract_WindowEtch)) AS Etch, 0,
      sum(dbo.fn_TotalGrossByDealer(iDealerID, dReportDate)) as Gross, 0, 0
FROM         dbo.tblReportData
WHERE  ProductionUnits > 0 and dReportDate between @dStart and @dEnd
group by iDealerID


SELECT iDealerID, ProductionUnits, Finance, FinanceRank, Life, LifeRank,
      Disab, DisabRank, Warr, WarrRank, Chem_2000, Chem_2000Rank,
      Etch, EtchRank, Gross, GrossRank, Points
FROM #tblComp
ORDER BY ProductionUnits Desc


drop table #tblComp

      RETURN

now for the problem. I set my rank columns to '0' as a placeholder and now I have to populate them. (see link) how can I get this done??

please (please) help. I've been pulling out my hair for a week now and just can't figure out how to get it to work. And I was supposed to deliver last Friday.

thank you
:) Lynda
LyndaPostalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mte01Commented:
>>Lynda

Stop pulling you hair :)

--Declare all variables used and not defined below
--Create a new table #tblComp2 with the same structure as #tblComp

DECLARE c CURSOR FOR
SELECT iDealerID, ProductionUnits, Finance, FinanceRank, Life, LifeRank,
     Disab, DisabRank, Warr, WarrRank, Chem_2000, Chem_2000Rank,
     Etch, EtchRank, Gross, GrossRank, Points
FROM #tblComp
ORDER BY Finance Asc

DELCARE @Count

OPEN c
SET @Count = 1
FETCH NEXT FROM c INTO @iDealerID, @ProductionUnits, @Finance, @FinanceRank, @Life, @LifeRank,
     @Disab, @DisabRank, @Warr, @WarrRank, @Chem_2000, @Chem_2000Rank,
     @Etch, @EtchRank, @Gross, @GrossRank, @Points

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tblComp2
VALUES(@iDealerID, @ProductionUnits, @Finance, @Count, @Life, @LifeRank,
     @Disab, @DisabRank, @Warr, @WarrRank, @Chem_2000, @Chem_2000Rank,
     @Etch, @EtchRank, @Gross, @GrossRank, @Points)

SET @Count = @Count + 1

FETCH NEXT FROM c INTO @iDealerID, @ProductionUnits, @Finance, @FinanceRank, @Life, @LifeRank,
     @Disab, @DisabRank, @Warr, @WarrRank, @Chem_2000, @Chem_2000Rank,
     @Etch, @EtchRank, @Gross, @GrossRank, @Points
END

CLOSE c
DEALLOCATE c

--Do the same for all other rankings
--Of course in the above and what you are going to continue on, there are a lot of variables that you won't need to fetch or insert
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LyndaPostalAuthor Commented:
except for a spelling error (DELCARE @Count) ;-P
and a few tweaks ...
worked well, thank you so much for your prompt response ...
if there were only an easier way to loop through a recordset and just set the fields; I hear I will be able to do that once I have installed SQL 2005. Not ready to do that, yet.

thanks again
:) L
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.