I am making a select from a table that has alums, but those alums can have a "level" being 01, 02, 03, 04, 05 or 99 and if I select a distinct(code) there will be 2 or 3 files of the same "code" with different "level". I want to be able to ONLY get the "code" with the higher "level" anytime.
Example of my code:
select
Level,
count(Code) as Atendee,
count(CASE WHEN CodPlace = 01 THEN (Atendee) ELSE null END ) as AtendeeCho,
count(CASE WHEN CodPlace = 02 THEN (Atendee) ELSE null END ) as AtendeeSB,
count(CASE WHEN CodPlace = 14 THEN (Atendee) ELSE null END ) as AtendeeIca
from #TempoPar
group by Level
order by Level
And here is the result:
Level Atendee AtendeeCho AtendeeSB AtendeeIca
1 3 2 0 1
2 0 0 0 0
3 2 2 0 0
99 1 1 0 0
Problem is that the one who is in AtendeeCho from level 1, 2 and 99 is the same person and he should only be on 99 or 3 if he wasn't on 99.
Thanks in advance!
Data example:
Code ( user) Level Career CodPlace
12345 1 9 01
12345 3 15 01
12346 1 10 14
12347 1 10 01
12345 3 15 01
12347 99 15 01
Now, this is what I require:
From the data sample:
Code ( user) Level Career CodPlace
12345 3 15 01
12346 1 10 14
12347 99 15 01
In the final output with the corrected data it should be: Level Atendee AtendeeCho AtendeeSB AtendeeIca
1 1 0 0 1
2 0 0 0 0
3 1 1 0 0
99 1 1 0 0