select * from (select 
                c.companyserial as companyserial,
                c.serial as serial,
                c.cameraname as cameraname,
                ifnull(cast(fcs.cameradegree as int),0) as cameradegree,
                chc.ceilingheight as mountheight,
                chc.serverguid as serverguid,
                ifnull(cast(chc.companyid as int),0) as companyid,
                ifnull(cast(chc.branchid as int),0) as branchid,
                ifnull(CU.mainentrance,0) as mainentrance,
                ifnull(CU.entrance,0) as entrance,
                ifnull(CU.queue,0) as queue,
                ifnull(CU.queuepickup,0) as queuepickup,
                ifnull(CU.heatmap,0) as heatmap,
                ifnull(CU.areacounting,0) as areacounting,
                ifnull(CU.seat,0) as seat,
                ifnull(CU.spaceoccupancy,0) as spaceoccupancy,
                ifnull(CU.wificounting,0) as wificounting
                from rccp.dbo.FFCameras c 
                inner join rccp.dbo.CounterHealthChecks chc 
                on c.Serial = chc.Serial and c.CompanySerial = chc.CompanySerial
                left join rccp.dbo.FFCameraSettings fcs
                on c.Id = fcs.CameraId
                left join (SELECT 
                    ServerGUID,
                    CounterSerial,
                    MAX(CASE WHEN UsageType = 'MainEntrance' THEN OccuredCount ELSE 0 END) AS MainEntrance,
                    MAX(CASE WHEN UsageType = 'Entrance' THEN OccuredCount ELSE 0 END) AS Entrance,
                    MAX(CASE WHEN UsageType = 'Queue' THEN OccuredCount ELSE 0 END) AS Queue,
                    MAX(CASE WHEN UsageType = 'QueuePickup' THEN OccuredCount ELSE 0 END) AS QueuePickup,
                    MAX(CASE WHEN UsageType = 'Heatmap' THEN OccuredCount ELSE 0 END) AS Heatmap,
                    MAX(CASE WHEN UsageType = 'AreaCounting' THEN OccuredCount ELSE 0 END) AS AreaCounting,
                    MAX(CASE WHEN UsageType = 'Seat' THEN OccuredCount ELSE 0 END) AS Seat,
                    MAX(CASE WHEN UsageType = 'SpaceOccupancy' THEN OccuredCount ELSE 0 END) AS SpaceOccupancy,
                    MAX(CASE WHEN UsageType = 'WifiCounting' THEN OccuredCount ELSE 0 END) AS WifiCounting
                FROM
                (
                    SELECT 
                        ServerGUID,
                        CounterSerial,
                        CASE 
                            WHEN UsageType = 1 THEN 'MainEntrance'
                            WHEN UsageType = 2 THEN 'Entrance'
                            WHEN UsageType = 3 THEN 'Queue'
                            WHEN UsageType = 4 THEN 'QueuePickup'
                            WHEN UsageType = 5 THEN 'Heatmap'
                            WHEN UsageType = 6 THEN 'AreaCounting'
                            WHEN UsageType = 7 THEN 'Seat'
                            WHEN UsageType = 8 THEN 'SpaceOccupancy'
                            WHEN UsageType = 9 THEN 'WifiCounting'
                            ELSE 'None' 
                        END AS UsageType,
                        1 AS OccuredCount
                    FROM 
                        rccp.dbo.CounterUsageConfigurations
                    WHERE 
                        (IsDeleted = 0 OR IsDeleted IS NULL)
                ) AS source
                GROUP BY 
                    ServerGUID, 
                    CounterSerial) CU 
                    on CU.CounterSerial = chc.Serial and chc.ServerGuid = CU.ServerGUID) a