/****** Script for SelectTopNRows command from SSMS ******/
select
max(a.latitude) as [latitude],
max(a.longitude) as [longitude],
a.ID as [ID] ,
max(a.regions) as [regions],
avg((case when a.[sim network] = '52003' and a.[Throughput_DL] is not null then a.
[Throughput_DL] else null end )) - avg((case when a.[sim network] = '52004' and a.
[Throughput_DL] is not null then a.[Throughput_DL] else null end )) as [GAP],
avg((case when a.[sim network] = '52003' and a.[Throughput_DL] is not null then a.
[Throughput_DL] else null end )) as [AIS],
avg((case when a.[sim network] = '52004' and a.[Throughput_DL] is not null then a.
[Throughput_DL] else null end )) as [TRUE-H]
from
SELECT
round([client_latitude],2) as [latitude],
round([client_longitude],2) as [longitude] ,
CAST(round([client_latitude],2) as varchar(20)) + '_' +
CAST(round([client_longitude],2) as varchar(20)) as [ID],
count(CAST(round([client_latitude],2) as varchar(20)) + '_' +
CAST(round([client_longitude],2) as varchar(20))) as [Sampling],
AVG ([download_kbps]) as [Throughput_DL],
AVG ([upload_kbps]) as [Throughput_UL],
case when [platform] = 'iOs' and [network_operator] = '52000' and
[sim_network_operator] = 'TRUE-H' then '52004'
when [platform] = 'iOs' then [network_operator] else sim_network_operator end
as [Sim network],
month(test_date) as [Month],
case
when left([Sub_districtcode],2) in ('10', '11', '12') then 'Bangkok'
when left([Sub_districtcode],2) in ('13', '14', '15','17','16', '70', '71',
'72', '73', '75', '76', '77') then 'Central'
when left([Sub_districtcode],2) in ('20', '21', '22', '23', '24', '25', '26',
'27') then 'East'
when left([Sub_districtcode],2) in ('50', '51', '52', '53', '54', '55', '56',
'57', '58', '60', '61', '62', '63', '64', '65', '66', '67') then 'North'
when left([Sub_districtcode],2) in ('30', '31', '32', '33', '34', '35', '36',
'37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49') then
'Northeast'
when left([Sub_districtcode],2) in ('80', '81', '82', '83', '84', '85', '86',
'90', '91', '92', '93', '94', '95', '96') then 'South' else null end as [Regions]
,
[Sub_districtcode] as [Sub_districtcode]
FROM [Speedtest_Raw_v2].[dbo].[Raw_Union_all_v1]
where post_connection_type in ('Lte','LTE','LTE','LTE-CA') and
post_connection_type = pre_connection_type and location_type = 1
and case when [platform] = 'iOs' and [network_operator] = '52000' and
[sim_network_operator] = 'TRUE-H' then '52004'
when [platform] = 'iOs' then [network_operator] else sim_network_operator end
in('52000','52004','52003') and
month(test_date) > 9
group by round([client_latitude],2) ,round([client_longitude],2) ,
sim_network_Operator,
case when [platform] = 'iOs' and [network_operator] = '52000' and
[sim_network_operator] = 'TRUE-H' then '52004'
when [platform] = 'iOs' then [network_operator] else sim_network_operator end
,month(test_date) ,round([client_latitude],2),round([client_longitude],2),
[Sub_districtcode],
case
when left([Sub_districtcode],2) in ('10', '11', '12') then 'Bangkok'
when left([Sub_districtcode],2) in ('13', '14', '15','17','16', '70', '71',
'72', '73', '75', '76', '77') then 'Central'
when left([Sub_districtcode],2) in ('20', '21', '22', '23', '24', '25', '26',
'27') then 'East'
when left([Sub_districtcode],2) in ('50', '51', '52', '53', '54', '55', '56',
'57', '58', '60', '61', '62', '63', '64', '65', '66', '67') then 'North'
when left([Sub_districtcode],2) in ('30', '31', '32', '33', '34', '35', '36',
'37', '38', '39', '40', '41', '42', '43', '44', '45', '46', '47', '48', '49') then
'Northeast'
when left([Sub_districtcode],2) in ('80', '81', '82', '83', '84', '85', '86',
'90', '91', '92', '93', '94', '95', '96') then 'South' else null end
) a
group by a.ID
