• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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
0
LyndaPostal
Asked:
LyndaPostal
1 Solution
 
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now