Location Cleanup

0

Please login or register to vote for this query.

(click on this box to dismiss)

Tridion Meta

Q&A about the site for Tridion developers and administrators

select Location, Cnt, CleanedLocation from
  (select Location, Cnt, 

  case 
    when Location like '%USA%' then 'USA'
    when Location like '%United States%' then 'USA'
    when Location like '%India%' then 'India'
    when Location like '%Germany%' then 'Germany'
    when Location like '%China%' then 'China'
    when Location like '%France%' then 'France'
    when Location like '%United Kingdom%' then 'United Kingdom'
    when Location like '%UK%' then 'United Kingdom'
    when Location like '%Philippines%' then 'Philippines'
    when Location like '%Indonesia%' then 'Indonesia'
    when Location like '%Singapore%' then 'Singapore'
    else 'null'
  end as CleanedLocation

  from
    (select Location, count(Location) as Cnt from Users where Location!='' group by Location) q1
) q2
where CleanedLocation='null'
order by Cnt desc

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results