Hello Team,
I have two files: apps and app_bucket (csv) files.
Certain apps are tested at certain times, so the apps tested are collected in a list. The tasks it to generate a count of the active apps from the lists of app ids, and I haven't a clue how best to do it.
Team help is appreciated Thank you.
Do you want to do this in SQL Server or in Excel? I can for SQL Server, but I don't have the expertise in Excel to give you the best way there.
Nope. You would count '10' as two entries that way. '100' would be three, '1000' would count as 4, etc.
the lord has been kind to me 
; with cte as
(
select * from apps , app_bucket
) , cte_crp as
(
select id,appname,value from cte CROSS apply string_split( replace(replace(bucket_list,'[',''),']',''), ',')
)
select id,count(*) from cte_crp where id = value group by id
go
create table apps
(
id int ,
appname varchar(100),
active int
)
insert into apps select 1,'Fintone',1
insert into apps select 2,'Pannier',1
create table app_bucket
(
apps_tested_id int ,
bucket_list varchar(100)
)
insert into app_bucket select 113, '[1,202,7,8]'
insert into app_bucket select 8 , '[1,10,9,6]'
select 'apps data',* from apps
select 'apps_bucket data',* from app_bucket