USE [MON2CM]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER VIEW [dbo].[EGSM_UL_IF_avg_rssi_d] AS
SELECT
a.Sitecode Sitecode,
a.Sitename Sitename,
a.SUBNETWORK,
b.MEID,
a.Userlabel Userlabel,
a.CAARFCNLIST CAARFNLIST,
CONVERT(VARCHAR(10),CAST(b.TIME AS datetime),126) DAY,
SUM(b.SampleNum) sum_SampleNum,
MAX(CASE WHEN b.Arfcn= 975 THEN b.Rssi END) ch_975,
MAX(CASE WHEN b.Arfcn= 976 THEN b.Rssi END) ch_976,
MAX(CASE WHEN b.Arfcn= 977 THEN b.Rssi END) ch_977,
MAX(CASE WHEN b.Arfcn= 978 THEN b.Rssi END) ch_978,
MAX(CASE WHEN b.Arfcn= 979 THEN b.Rssi END) ch_979,
MAX(CASE WHEN b.Arfcn= 980 THEN b.Rssi END) ch_980,
MAX(CASE WHEN b.Arfcn= 981 THEN b.Rssi END) ch_981,
MAX(CASE WHEN b.Arfcn= 982 THEN b.Rssi END) ch_982,
MAX(CASE WHEN b.Arfcn= 983 THEN b.Rssi END) ch_983,
MAX(CASE WHEN b.Arfcn= 984 THEN b.Rssi END) ch_984,
MAX(CASE WHEN b.Arfcn= 985 THEN b.Rssi END) ch_985,
MAX(CASE WHEN b.Arfcn= 986 THEN b.Rssi END) ch_986,
MAX(CASE WHEN b.Arfcn= 987 THEN b.Rssi END) ch_987,
MAX(CASE WHEN b.Arfcn= 988 THEN b.Rssi END) ch_988,
MAX(CASE WHEN b.Arfcn= 989 THEN b.Rssi END) ch_989,
MAX(CASE WHEN b.Arfcn= 990 THEN b.Rssi END) ch_990,
MAX(CASE WHEN b.Arfcn= 991 THEN b.Rssi END) ch_991,
MAX(CASE WHEN b.Arfcn= 992 THEN b.Rssi END) ch_992,
MAX(CASE WHEN b.Arfcn= 993 THEN b.Rssi END) ch_993,
MAX(CASE WHEN b.Arfcn= 994 THEN b.Rssi END) ch_994,
MAX(CASE WHEN b.Arfcn= 995 THEN b.Rssi END) ch_995,
MAX(CASE WHEN b.Arfcn= 996 THEN b.Rssi END) ch_996,
MAX(CASE WHEN b.Arfcn= 997 THEN b.Rssi END) ch_997,
MAX(CASE WHEN b.Arfcn= 998 THEN b.Rssi END) ch_998,
MAX(CASE WHEN b.Arfcn= 999 THEN b.Rssi END) ch_999,
MAX(CASE WHEN b.Arfcn= 1000 THEN b.Rssi END) ch_1000,
MAX(CASE WHEN b.Arfcn= 1001 THEN b.Rssi END) ch_1001,
MAX(CASE WHEN b.Arfcn= 1002 THEN b.Rssi END) ch_1002,
MAX(CASE WHEN b.Arfcn= 1003 THEN b.Rssi END) ch_1003,
MAX(CASE WHEN b.Arfcn= 1004 THEN b.Rssi END) ch_1004,
MAX(CASE WHEN b.Arfcn= 1005 THEN b.Rssi END) ch_1005,
MAX(CASE WHEN b.Arfcn= 1006 THEN b.Rssi END) ch_1006,
MAX(CASE WHEN b.Arfcn= 1007 THEN b.Rssi END) ch_1007,
MAX(CASE WHEN b.Arfcn= 1008 THEN b.Rssi END) ch_1008,
MAX(CASE WHEN b.Arfcn= 1009 THEN b.Rssi END) ch_1009,
MAX(CASE WHEN b.Arfcn= 1010 THEN b.Rssi END) ch_1010,
MAX(CASE WHEN b.Arfcn= 1011 THEN b.Rssi END) ch_1011,
MAX(CASE WHEN b.Arfcn= 1012 THEN b.Rssi END) ch_1012,
MAX(CASE WHEN b.Arfcn= 1013 THEN b.Rssi END) ch_1013,
MAX(CASE WHEN b.Arfcn= 1014 THEN b.Rssi END) ch_1014,
MAX(CASE WHEN b.Arfcn= 1015 THEN b.Rssi END) ch_1015,
MAX(CASE WHEN b.Arfcn= 1016 THEN b.Rssi END) ch_1016,
MAX(CASE WHEN b.Arfcn= 1017 THEN b.Rssi END) ch_1017,
MAX(CASE WHEN b.Arfcn= 1018 THEN b.Rssi END) ch_1018,
MAX(CASE WHEN b.Arfcn= 1019 THEN b.Rssi END) ch_1019,
MAX(CASE WHEN b.Arfcn= 1020 THEN b.Rssi END) ch_1020,
MAX(CASE WHEN b.Arfcn= 1021 THEN b.Rssi END) ch_1021,
MAX(CASE WHEN b.Arfcn= 1022 THEN b.Rssi END) ch_1022,
MAX(CASE WHEN b.Arfcn= 1023 THEN b.Rssi END) ch_1023,
MAX(CASE WHEN b.Arfcn= 0 THEN b.Rssi END) ch_0,
MAX(CASE WHEN b.Arfcn= 1 THEN b.Rssi END) ch_1,
MAX(CASE WHEN b.Arfcn= 2 THEN b.Rssi END) ch_2,
MAX(CASE WHEN b.Arfcn= 3 THEN b.Rssi END) ch_3,
MAX(CASE WHEN b.Arfcn= 4 THEN b.Rssi END) ch_4,
MAX(CASE WHEN b.Arfcn= 5 THEN b.Rssi END) ch_5,
MAX(CASE WHEN b.Arfcn= 6 THEN b.Rssi END) ch_6,
MAX(CASE WHEN b.Arfcn= 7 THEN b.Rssi END) ch_7,
MAX(CASE WHEN b.Arfcn= 8 THEN b.Rssi END) ch_8,
MAX(CASE WHEN b.Arfcn= 9 THEN b.Rssi END) ch_9,
MAX(CASE WHEN b.Arfcn= 10 THEN b.Rssi END) ch_10,
MAX(CASE WHEN b.Arfcn= 11 THEN b.Rssi END) ch_11,
MAX(CASE WHEN b.Arfcn= 12 THEN b.Rssi END) ch_12,
MAX(CASE WHEN b.Arfcn= 13 THEN b.Rssi END) ch_13,
MAX(CASE WHEN b.Arfcn= 14 THEN b.Rssi END) ch_14,
MAX(CASE WHEN b.Arfcn= 15 THEN b.Rssi END) ch_15,
MAX(CASE WHEN b.Arfcn= 16 THEN b.Rssi END) ch_16,
MAX(CASE WHEN b.Arfcn= 17 THEN b.Rssi END) ch_17,
MAX(CASE WHEN b.Arfcn= 18 THEN b.Rssi END) ch_18,
MAX(CASE WHEN b.Arfcn= 19 THEN b.Rssi END) ch_19,
MAX(CASE WHEN b.Arfcn= 20 THEN b.Rssi END) ch_20,
MAX(CASE WHEN b.Arfcn= 21 THEN b.Rssi END) ch_21,
MAX(CASE WHEN b.Arfcn= 22 THEN b.Rssi END) ch_22,
MAX(CASE WHEN b.Arfcn= 23 THEN b.Rssi END) ch_23,
MAX(CASE WHEN b.Arfcn= 24 THEN b.Rssi END) ch_24,
MAX(CASE WHEN b.Arfcn= 25 THEN b.Rssi END) ch_25,
MAX(CASE WHEN b.Arfcn= 26 THEN b.Rssi END) ch_26,
MAX(CASE WHEN b.Arfcn= 27 THEN b.Rssi END) ch_27,
MAX(CASE WHEN b.Arfcn= 28 THEN b.Rssi END) ch_28,
MAX(CASE WHEN b.Arfcn= 29 THEN b.Rssi END) ch_29,
MAX(CASE WHEN b.Arfcn= 30 THEN b.Rssi END) ch_30,
MAX(CASE WHEN b.Arfcn= 31 THEN b.Rssi END) ch_31,
MAX(CASE WHEN b.Arfcn= 32 THEN b.Rssi END) ch_32,
MAX(CASE WHEN b.Arfcn= 33 THEN b.Rssi END) ch_33,
MAX(CASE WHEN b.Arfcn= 34 THEN b.Rssi END) ch_34,
MAX(CASE WHEN b.Arfcn= 35 THEN b.Rssi END) ch_35,
MAX(CASE WHEN b.Arfcn= 36 THEN b.Rssi END) ch_36,
MAX(CASE WHEN b.Arfcn= 37 THEN b.Rssi END) ch_37,
MAX(CASE WHEN b.Arfcn= 38 THEN b.Rssi END) ch_38,
MAX(CASE WHEN b.Arfcn= 39 THEN b.Rssi END) ch_39,
MAX(CASE WHEN b.Arfcn= 40 THEN b.Rssi END) ch_40,
MAX(CASE WHEN b.Arfcn= 41 THEN b.Rssi END) ch_41,
MAX(CASE WHEN b.Arfcn= 42 THEN b.Rssi END) ch_42,
MAX(CASE WHEN b.Arfcn= 43 THEN b.Rssi END) ch_43,
MAX(CASE WHEN b.Arfcn= 44 THEN b.Rssi END) ch_44,
MAX(CASE WHEN b.Arfcn= 45 THEN b.Rssi END) ch_45,
MAX(CASE WHEN b.Arfcn= 46 THEN b.Rssi END) ch_46,
MAX(CASE WHEN b.Arfcn= 47 THEN b.Rssi END) ch_47,
MAX(CASE WHEN b.Arfcn= 48 THEN b.Rssi END) ch_48,
MAX(CASE WHEN b.Arfcn= 49 THEN b.Rssi END) ch_49,
MAX(CASE WHEN b.Arfcn= 50 THEN b.Rssi END) ch_50,
MAX(CASE WHEN b.Arfcn= 51 THEN b.Rssi END) ch_51,
MAX(CASE WHEN b.Arfcn= 52 THEN b.Rssi END) ch_52,
MAX(CASE WHEN b.Arfcn= 53 THEN b.Rssi END) ch_53,
MAX(CASE WHEN b.Arfcn= 54 THEN b.Rssi END) ch_54,
MAX(CASE WHEN b.Arfcn= 55 THEN b.Rssi END) ch_55,
MAX(CASE WHEN b.Arfcn= 56 THEN b.Rssi END) ch_56,
MAX(CASE WHEN b.Arfcn= 57 THEN b.Rssi END) ch_57,
MAX(CASE WHEN b.Arfcn= 58 THEN b.Rssi END) ch_58,
MAX(CASE WHEN b.Arfcn= 59 THEN b.Rssi END) ch_59,
MAX(CASE WHEN b.Arfcn= 60 THEN b.Rssi END) ch_60,
MAX(CASE WHEN b.Arfcn= 61 THEN b.Rssi END) ch_61,
MAX(CASE WHEN b.Arfcn= 62 THEN b.Rssi END) ch_62,
MAX(CASE WHEN b.Arfcn= 63 THEN b.Rssi END) ch_63,
MAX(CASE WHEN b.Arfcn= 64 THEN b.Rssi END) ch_64,
MAX(CASE WHEN b.Arfcn= 65 THEN b.Rssi END) ch_65,
MAX(CASE WHEN b.Arfcn= 66 THEN b.Rssi END) ch_66,
MAX(CASE WHEN b.Arfcn= 67 THEN b.Rssi END) ch_67,
MAX(CASE WHEN b.Arfcn= 68 THEN b.Rssi END) ch_68,
MAX(CASE WHEN b.Arfcn= 69 THEN b.Rssi END) ch_69,
MAX(CASE WHEN b.Arfcn= 70 THEN b.Rssi END) ch_70,
MAX(CASE WHEN b.Arfcn= 71 THEN b.Rssi END) ch_71,
MAX(CASE WHEN b.Arfcn= 72 THEN b.Rssi END) ch_72,
MAX(CASE WHEN b.Arfcn= 73 THEN b.Rssi END) ch_73,
MAX(CASE WHEN b.Arfcn= 74 THEN b.Rssi END) ch_74,
MAX(CASE WHEN b.Arfcn= 75 THEN b.Rssi END) ch_75,
MAX(CASE WHEN b.Arfcn= 76 THEN b.Rssi END) ch_76,
MAX(CASE WHEN b.Arfcn= 77 THEN b.Rssi END) ch_77,
MAX(CASE WHEN b.Arfcn= 78 THEN b.Rssi END) ch_78,
MAX(CASE WHEN b.Arfcn= 79 THEN b.Rssi END) ch_79
FROM
MON2CM.dbo.CM_2G_CELL a
LEFT JOIN MON2M.dbo.ULSCAN b
ON a.BTSID = b.SectorNo
AND
LEFT(a.SUBNETWORK, CASE WHEN charindex(',', a.SUBNETWORK) = 0 THEN LEN(a.SUBNETWORK) ELSE charindex(',', a.SUBNETWORK) - 1 END) = b.id
AND
LEFT(a.MEID, CASE WHEN charindex(',', a.MEID) = 0 THEN LEN(a.MEID) ELSE charindex(',', a.MEID) - 1 END) = b.MEID
GROUP BY CONVERT(VARCHAR(10),CAST(b.TIME AS datetime),126), a.Sitename, a.Sitecode, b.MEID, a.Userlabel, a.CAARFCNLIST, a.SUBNETWORK


GO