q uid response codes

q

insert  into x_devices
SELECT level2
	,'Jun' Month
	,sum(installed) installed
	,sum(connected) connected
	,sum(transacted) transacted
FROM (
	SELECT level2
		,1 installed
		,CASE 
			WHEN ard_no IS NOT NULL
				THEN 1
			ELSE 0
			END connected
		,CASE 
			WHEN ard_no IS NOT NULL
				AND transacted = 1
				THEN 1
			ELSE 0
			END transacted
	FROM ard_mapping_history a
	LEFT JOIN (
		SELECT ard_no
			,1 connected
			,sum(transacted) transacted
		FROM (
			SELECT ard_no
				,0 transacted
			FROM device_info_p
			WHERE month(date_time) = 6
			UNION
			SELECT ard_no
				,1 transacted
			FROM transaction_master_p
			WHERE month(CONVERT(DATETIME, TXNDATETIME, 120)) =6
				AND STATUS = 1
			UNION
			SELECT fps_dealer_id
				,0 transacted
			FROM ard_mapping_history
			WHERE device_id IN (
					SELECT hhid
					FROM uid_response_p
					WHERE month(INSERTED_DT) = 6
					)
			) x
		GROUP BY ard_no
		) x ON a.fps_dealer_id = x.ard_no
		WHERE FROM_DATE < '2017-06-30'
	) x
GROUP BY level2