Kongta
asked on
Percentage Ranking = Points
I would like to sort a list of data where all data respresents 100% and then would like to allot each data the full-percentage-point (uprounded) in a separate field like
Symbol P1_1 [%] F1_1
abc 0.15 0.02% 1
aed 0.16 0.04% 1
....
edx 5.43 97.9% 98
bde 6.15 100% 100
I call the data by
SELECT Symbol, P1_1
FROM dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?
No idea if my thoughts can be placed in a code but appreciate any ideas
thx
Kongta
Symbol P1_1 [%] F1_1
abc 0.15 0.02% 1
aed 0.16 0.04% 1
....
edx 5.43 97.9% 98
bde 6.15 100% 100
I call the data by
SELECT Symbol, P1_1
FROM dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?
No idea if my thoughts can be placed in a code but appreciate any ideas
thx
Kongta
ASKER
I do only have P1_1 and now looking for first to generate the percentage ranking and after to allot the points out of the percentage ranking and if possible, an option to avoid the outlier
How do you calculate the percentage?
do i understand the question correct:
you first want to calculate the % point column as follows ( P1_1 / Max (P1_1))
based on this result , remove lines from further calculation that have a
% < 0.5
and
% > 99.5 ( ie ignore the botom 0.5% vlaues and the top 0.5% values)
then from this reduced resultset, generate the F1_1 values.
so in you example the resultset will not contain abc,aed or the bde rows
you first want to calculate the % point column as follows ( P1_1 / Max (P1_1))
based on this result , remove lines from further calculation that have a
% < 0.5
and
% > 99.5 ( ie ignore the botom 0.5% vlaues and the top 0.5% values)
then from this reduced resultset, generate the F1_1 values.
so in you example the resultset will not contain abc,aed or the bde rows
ASKER
FemSteenkamp, the percentage relies on the ranking of each and not in relation to the MAX(P1_1) like if we have 100 data for example, then
(Rank) Symbol P1_1 [%] F1_1
1 abc 0.15 1% 1
2 aed 0.16 2% 2
....
98 edx 5.43 98% 98
100 bde 6.15 100% 100
I would like to screen my 20'000 symbols on different data and at the end find the best third. I will after create an UPDATE/SET event therefore it has to contain abd,aed...
(Rank) Symbol P1_1 [%] F1_1
1 abc 0.15 1% 1
2 aed 0.16 2% 2
....
98 edx 5.43 98% 98
100 bde 6.15 100% 100
I would like to screen my 20'000 symbols on different data and at the end find the best third. I will after create an UPDATE/SET event therefore it has to contain abd,aed...
ASKER
mattclarified:How do you calculate the percentage? no idea yet
SELECT Symbol, P1_1,
cast (P1_1/max(P1_1) as numeric(18,6)) as[%],
cast(round ((P1_1/max(P1_1))* 100,0) as numeric(18,0)) as F1_1
FROM dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
I believe this is what you're after.
Hope it helps ;)
Dan
cast (P1_1/max(P1_1) as numeric(18,6)) as[%],
cast(round ((P1_1/max(P1_1))* 100,0) as numeric(18,0)) as F1_1
FROM dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
I believe this is what you're after.
Hope it helps ;)
Dan
-- OK attempt two -- I reread all the above and here is a second atttempt... let me know !!
CREATE TABLE #Rank (
Symbol nvarchar(10),
P1_1 numeric (18,6),
[%] numeric (18,6),
RankOrder int identity(1,1)
)
Insert into #Rank (Symbol, P1_1, [%])
Select Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
into #Rank
from dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)
Select *
from #Rank
where [%] > 0.5 and [%] < 99.5
CREATE TABLE #Rank (
Symbol nvarchar(10),
P1_1 numeric (18,6),
[%] numeric (18,6),
RankOrder int identity(1,1)
)
Insert into #Rank (Symbol, P1_1, [%])
Select Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
into #Rank
from dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)
Select *
from #Rank
where [%] > 0.5 and [%] < 99.5
ASKER
get an error on 'into'
I now created a tblRank as I can't see the created table #Rank in my SQL, guess this is a tmpTbl? I'm not used to this, I ran once before but can't see, do I now have somewhere a ghost-tbl?
I now created a tblRank as I can't see the created table #Rank in my SQL, guess this is a tmpTbl? I'm not used to this, I ran once before but can't see, do I now have somewhere a ghost-tbl?
ASKER
I skipped the second 'into' on line 3, guess this is wrong, still get an error saying Symbol is not valid as it's not in an aggregate-function and not in a group-by?!
ASKER
Insert into tblRank (Symbol, P_1_1, [%])
SELECT Symbol, P_1_1, CAST(P_1_1 / MAX(P_1_1) AS numeric(18, 6)) AS [%]
FROM tblModellTmp
WHERE (NOT (P_1_1 IS NULL))
GROUP BY Symbol, P_1_1
ORDER BY P_1_1 / MAX(P_1_1)
Select *
from tblRank
where [%] > 0.5 and [%] < 99.5
RESULT:
ZMD -3.000000 1.000000 NULL
ZMH 14.517565 1.000000 NULL
ZMNO 5.294117 1.000000 NULL
ZMNO 6.049827 1.000000 NULL
ZQK 18.583334 1.000000 NULL
ZRAN 403.571442 1.000000 NULL
ZTR 5.186781 1.000000 NULL
changed dbo.GFHilfstabelleV1 into dbo.GFHilfstabelleV1 and P1_1 into P_1_1 as I used a more up-to-date.db. but there are no results
SELECT Symbol, P_1_1, CAST(P_1_1 / MAX(P_1_1) AS numeric(18, 6)) AS [%]
FROM tblModellTmp
WHERE (NOT (P_1_1 IS NULL))
GROUP BY Symbol, P_1_1
ORDER BY P_1_1 / MAX(P_1_1)
Select *
from tblRank
where [%] > 0.5 and [%] < 99.5
RESULT:
ZMD -3.000000 1.000000 NULL
ZMH 14.517565 1.000000 NULL
ZMNO 5.294117 1.000000 NULL
ZMNO 6.049827 1.000000 NULL
ZQK 18.583334 1.000000 NULL
ZRAN 403.571442 1.000000 NULL
ZTR 5.186781 1.000000 NULL
changed dbo.GFHilfstabelleV1 into dbo.GFHilfstabelleV1 and P1_1 into P_1_1 as I used a more up-to-date.db. but there are no results
i thought you said the % calculation is not done by P_1_1, CAST(P_1_1 / MAX(P_1_1) ???
so your comment
and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?
so not sure what you trying to do here then since now tou are trying code where you are calculating the % the way you said it is not done?
so try and explain what you are trying to do a bit better with more examples if you want more assistance
so your comment
and would like to SET F1_1 later with the points. As there are always some outlier, would there be a way to skip the first/last half percent from the ground data?
so not sure what you trying to do here then since now tou are trying code where you are calculating the % the way you said it is not done?
so try and explain what you are trying to do a bit better with more examples if you want more assistance
drop table tblRank
CREATE TABLE tblRank (
Symbol nvarchar(10),
P1_1 numeric (18,6),
[%] numeric (18,6),
RankOrder int identity(1,1)
)
-- Only run the table delete and Create statement above once
-- Yes #Rank is a temp table -- The Identity(1,1) is the key here - this will add the numbers to the field as the records are inserted into the temp table (#Rank) -- try this again -- oversite on my part
-- If you use your tabel tblRank -- add a field and add identity to it
Insert into tblRank (Symbol, P1_1, [%])
Select Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
from dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)
Select *
from tblRank
where [%] > 0.5 and [%] < 99.5
CREATE TABLE tblRank (
Symbol nvarchar(10),
P1_1 numeric (18,6),
[%] numeric (18,6),
RankOrder int identity(1,1)
)
-- Only run the table delete and Create statement above once
-- Yes #Rank is a temp table -- The Identity(1,1) is the key here - this will add the numbers to the field as the records are inserted into the temp table (#Rank) -- try this again -- oversite on my part
-- If you use your tabel tblRank -- add a field and add identity to it
Insert into tblRank (Symbol, P1_1, [%])
Select Symbol, P1_1,cast (P1_1/max(P1_1) as numeric(18,6)) as[%]
from dbo.GFHilfstabelleV1
WHERE (NOT (P1_1 IS NULL))
order by P1_1/max(P1_1)
Select *
from tblRank
where [%] > 0.5 and [%] < 99.5
ASKER
okay, sorry for the confusion, I'm not English and is hard to explain:
I do have following ground data
Symbol P_1_1
ZMH 14.517565
ZMNO 5.294117
ZMNO 6.049827
ZQK 18.583334
ZRAN 403.571442
ZTR 5.186781
the lowest value ist best. I now would like to order my 20'000 datas by P_1_1 and find out, at what percentage place each data lies. A workaround may by having a count-field, so first data gets '1', the worst '20000' and after go for 1/20000 = 0.00005 (=0.05%). Having this value, I would upround and save it in field F_1_1. Therefore, the best ones in class gets 1 and the worst ones in class gets 100. Doing this with these data on more values, at the end I get a ranking where I see which one is best on choosable values.
Let me know what I have to explain more, thx
I do have following ground data
Symbol P_1_1
ZMH 14.517565
ZMNO 5.294117
ZMNO 6.049827
ZQK 18.583334
ZRAN 403.571442
ZTR 5.186781
the lowest value ist best. I now would like to order my 20'000 datas by P_1_1 and find out, at what percentage place each data lies. A workaround may by having a count-field, so first data gets '1', the worst '20000' and after go for 1/20000 = 0.00005 (=0.05%). Having this value, I would upround and save it in field F_1_1. Therefore, the best ones in class gets 1 and the worst ones in class gets 100. Doing this with these data on more values, at the end I get a ranking where I see which one is best on choosable values.
Let me know what I have to explain more, thx
ASKER
ran delete/create once, but can't follow on 'add a field and add identity to it ' as I can't choose 'identity', I made RankOrder as Key
after had following error
Meldung 8152, Ebene 16, Status 13, Zeile 3
Zeichenfolgen- oder Binärdaten was cut.
(0 Zeile(n) betroffen)
after had following error
Meldung 8152, Ebene 16, Status 13, Zeile 3
Zeichenfolgen- oder Binärdaten was cut.
(0 Zeile(n) betroffen)
try the below
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0)
from (
SELECT Symbol, P1_1, row_number() over (order by P1_1) as rnk
FROM dbo.GFHilfstabelleV1
WHERE P1_1 is not null
) a
ASKER
nearly perfect ralmada, here a sample of the middle of the list. Where would you place the filter that the best 0,5% and the wort 0,5% are not part of the selection. I guess it's more honest to filter already at the basis, meaning that the 100% are represented by the 0,5% - 99,5% of P_1_1, not or does this make it more difficult?
PRM US 0 0.299682834
PRKR US 0 0.299760192
HPCL IN 2.06E-07 0.299837549
910 HK 0.009394905 0.299914907
CTDB US 0.0248 0.299992264
PRM US 0 0.299682834
PRKR US 0 0.299760192
HPCL IN 2.06E-07 0.299837549
910 HK 0.009394905 0.299914907
CTDB US 0.0248 0.299992264
Something like this?
;with CTE as (
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P1_1, row_number() over (order by P1_1) as rnk
FROM dbo.GFHilfstabelleV1
WHERE P1_1 is not null
)
select * from CTE
where perc between 0.05 and 0.995
The above will consider the .05 and .995 too, if you want to exclude them also, then do like this
;with CTE as (
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P1_1, row_number() over (order by P1_1) as rnk
FROM dbo.GFHilfstabelleV1
WHERE P1_1 is not null
)
select * from CTE
where perc > 0.05 and perc < 0.995
ASKER
I get: 'Wrong syntax near Select' on line 8
missed one bracket
;with CTE as (
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P1_1, row_number() over (order by P1_1) as rnk
FROM dbo.GFHilfstabelleV1
WHERE P1_1 is not null
)
)
select * from CTE
where perc > 0.05 and perc < 0.995
oh, well. Here is the correct one
;with CTE as (
select Symbol, P1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P1_1, row_number() over (order by P1_1) as rnk
FROM dbo.GFHilfstabelleV1
WHERE P1_1 is not null
) a
)
select * from CTE
where perc > 0.05 and perc < 0.995
ASKER
works, thx, one last hurdle: How to I place your code as a SET event?
UPDATE tblModellTmp
SET F_1_1 = perc ??
;with CTE as (
select Symbol, P_1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P_1_1, row_number() over (order by P_1_1) as rnk
FROM tblModellTmp
WHERE P_1_1 is not null
) a
)
select * from CTE
where perc > 0.05 and perc < 0.995
UPDATE tblModellTmp
SET F_1_1 = perc ??
;with CTE as (
select Symbol, P_1_1, rnk * 1.0 / max(rnk) over (partition by 0) as perc
from (
SELECT Symbol, P_1_1, row_number() over (order by P_1_1) as rnk
FROM tblModellTmp
WHERE P_1_1 is not null
) a
)
select * from CTE
where perc > 0.05 and perc < 0.995
ASKER
just learned about row_number() thx of you. can you explain me what the intention is with 'rnk * 1.0' wouldn't 'rnk / max(rnk)' be same?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool, have many thx for your great support, appreciate much
rgds
Kongta
rgds
Kongta
Konqta or Ralmada can you show me the entire final code?
I can't figure out how to start TSQL using a "; with CTE"
I can't figure out how to start TSQL using a "; with CTE"
ASKER
I copy/paste the code and it worked right away
maybe Ralmada can give a hint
maybe Ralmada can give a hint
I get it now -- thanks Ralmada -- The "with CTE" is new to me... that will save me tons of coding.
You definitely proved why your rank is genius!
You definitely proved why your rank is genius!
you're welcome.
All you need to do is put the formula =ROUNDUP(C1,0) into D1 for it to round up the points value for you
M@