I've started a new topic for this, as the old was is a different problem, lol.
This script is working now, however it says the unitIndex is 287 for all units.
What I wanted the unitIndex to be is, 1 for the unit with highest tsr, and (if 30 units) 30 for the unit with lowest tsr.
Why is it making the UnitIndex 287, and how can I fix it? lol
(I have marked the line where the unitIndex is
PROCEDURE units_display (@startIndex INT, @endIndex INT)
declare @maxrts bigint;
select @maxrts = (select Distinct max((((wins)*((wins*100)/((games+1)*4))))) from unit);
if @maxrts = 0
set @maxrts = 1;
declare @maxValue bigint;
from (select *, (select unit from UnitTypes where UnitTypes.unitid = unit.UnitType) as unitTypeName, ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs2 from Unit) as Unit1
(select Unit3.trs, count(*) as UnitIndex --< here is UnitIndex
(select ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs from Unit) as unit3
(select ((@maxValue*((wins)*((wins*100)/((games+1)*4))))/@maxrts ) as trs3 from Unit) as Unit2 on Unit3.trs <= Unit2.trs3
group by Unit3.trs) as UnitIndexes
on Unit1.trs2 = UnitIndexes.trs
where UnitIndexes.UnitIndex between @StartIndex and @EndIndex
order by Unit1.trs2 DESC
I am only looking at one table, I want to select all the fields from it, but relevant fields are:
unitid int pk
unittype int (fk referencing UnitTypes, unitid)
unitid int PK
tsr is a column I am working out on the fly, it isn't stored in the database. perhaps a better method would be to create a view first with tsr in it, then run the reorder etc after, however I am unsure how views work, and as of yet, I havn't had time to look into them to determine if they are what I should use.
Any help simplifying this would be awesome, lol.
I have solved the issue I had with the script below, however if you can should me however, it is just a repair on the code as it is there, lol
I'll post the repair in here if you'd like to work from that. Basically though, once I determine the column tsr, I then want to determine the 'ranks' of each unit. So basically, units with the same tsr score, get the worse rank, ie, the third and fourth highest scores both get rank 4 (rank = unitindex form the script below). once that is done, I then was to assign a position variable to each row.
I am doing the same thing with a similar query, which a few people are discussing with me in another post. From what they have said, I will be looking at creating a table, with the columns i desire, plus am incrementing 'position' column.
I will then use something like
insert into @tempTable (the select statement which returns a reordered table with the tsr value).