asked on

SQL - Formula to count the top 3 usage

Dear Experts,

Having a complex query result and searching for a formula in SQL, which could determine the top 3 countries in usage.

If you run the following code (Microsoft SQL Server) that setups the example,

CREATE TABLE
   #TempTable (
      FieldType nvarchar(20),
      FieldUsage nvarchar(500)
      );

INSERT INTO
   #TempTable (FieldType, FieldUsage)
VALUES
   ('Field1', 'DE Germany 18799 /US USA 11913 /BR Brazil 9840 /CN China 9436 /IN India 9113 /GB United Kingdom 8910'),
   ('Field2', 'CN China 446 /DE Germany 102'),
   ('Field3', 'PL Poland 91 /US USA 44 /FR France 43 /IN India 22 /AR Argentina 17 /SE Sweden 16')

SELECT 
   * 
FROM 
   #TempTable

/*

DELETE FROM
   #TempTable

DROP TABLE
   #TempTable

*/

Open in new window

which is so in column FieldUsage a pattern of Country code + Country name + CountOfFields used with separating '/' character (if you look on the numbers they are decreasing, so the top usage is the first country, then comes the second etc.):

User generated image

The target would be an SQL formula which results as it is shown on the below picture in column C, briefly cutting the LEFT three countries from column B (if there are just two countries, then two) to determine the top 3 usage:

User generated image

Could you please advise which SQL formula could result that?

In the real environment the FieldUsage field can be very long, sometimes listing 30-40 countries (meaning that 30-40 times being the '/' separator there)

Thanks in advance,

slightwv (䄆 Netminder)

Your SQL Server version would help a lot.

As long as FieldUsage is ALWAYS in the order you want, what you are really after is to 'trim' everything after the third '/'.

This works on the data provided. It uses a recursive CTE. As long as there isn't a LOT of rows, it should be fine. They don't scale well.

with recursive_cte as (
    select 1 as row, FieldType, charindex('/', FieldUsage) offset, FieldUsage + '/' FieldUsage
    from #TempTable
    union all
    select row + 1, FieldType, charindex('/', FieldUsage, offset + 1), FieldUsage
    from recursive_cte
    where offset>0 and row<=2
),
get_correct_row as (
	select row, FieldType, FieldUsage, offset, row_number() over(partition by FieldType order by offset desc) rn
	from recursive_cte 
)
select 
FieldType, FieldUsage, SubString(FieldUsage,1,offset-1) Top3Countries
from get_correct_row
where rn=1

Open in new window

My test Fiddle is here:
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7e429c26d7c97497a9e736c9ac3391b1

PortletPaul

CHARINDEX() will return the position of a string within a string. Using a combination of those will locate the third position of '/' and then LEFT() can be used to output that portion of the source string.

I have used an OUTER APPLY just in case there are missing strings and a case expression if there isn't a value returned by the apply.

select
     FieldType
   , FieldUsage
   , case when oa.pos > 0 then left(FieldUsage,oa.pos-1) end as Top3Countries
from #TempTable
outer apply (select charindex('/',FieldUsage,charindex('/',FieldUsage,charindex('/',FieldUsage,1)+1)+1) as pos
                    ) oa

Open in new window

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csehz

ASKER

Thank you very much for the solutions and advice