Help needed optimizing this SP

Hi All,

I've inherited this stored proc and I'm trying to improve the performance of it. I really hate a couple of things about the way it was written.

First, I hate that it's broken into two inserts rather than one insert with the selects done as a union between the two selects.

Second, I hate that each query uses two other subqueries. I'm thinking of changing that to either CTEs or possibly temp tables. I'm just not sure which is the better way to go, so I'm asking the SQL community at large.

CREATE PROCEDURE [stg].[p_ads_Staging_ScanSummary_Load] AS

BEGIN	

TRUNCATE TABLE ADS.STG.Staging_ScanSummary

INSERT INTO ADS.STG.Staging_ScanSummary

SELECT 
				   Region,
				   Company,
				   DepartmentCode,
				   ModalityID,
				   CustomerNbr,
				   Asset_id,
				   Servicedate,
				   Date_ID,
				   PostDate,
				   [retail_scan_count],
				   '0' AS Wholesale_scan_count,
				   '0' AS wbsscancount,
				   [retail_scan_count] AS Nbrscans,
				   'Retail' as Loadsource
FROM (
SELECT 
				   Region,
				   Company,
				   DepartmentCode,
				   ModalityID,
				   CustomerNbr,
				   Asset_id,
				   Servicedate,
				   Date_ID,
				   PostDate,
				   sum([retail_scan_count]) AS [retail_scan_count]
				   
				FROM
	
				(SELECT 
				   o.sublatcode AS Region,
				   C.CompanyCode AS Company,
				   D.DepartmentCode AS DepartmentCode,
				   Isnull(M.ModalityID,CSN.ModalityID) AS ModalityID,
				   CONVERT(VARCHAR(50),IGY.CustNmbr) AS CustomerNbr,
				   ISNULL(CA.UnitNbr,'0000') AS Asset_id,
				   IGY.DOS AS Servicedate,
				   (SELECT P.DateID FROM ADW.dim.Period P WHERE P.[Date] = IGY.DOS) AS Date_ID,
				   (SELECT P.DateID FROM ADW.dim.Period P WHERE P.[Date] = IGY.PostDate) AS PostDate,
				   IGY.[ScanCount] AS [retail_scan_count]

			FROM	[ADS].[dbo].[Intergy_RetailRevenueDetailAINEHE] IGY
			JOIN ADW.DIM.CustomerServiceNumber CSN
			ON CONVERT(VARCHAR(50),IGY.CustNmbr) = CSN.CustomerServiceNumber
			JOIN ADW.dim.Company C
			ON CSN.CompanyID = C.CompanyID
			LEFT JOIN ADW.dim.Modality M
			ON IGY.modality = M.modalitycode
			JOIN ADW.dim.Department D
			ON D.DepartmentID = CASE WHEN M.departmentID is null then (select MO.departmentID from adw.dim.Modality MO where MO.ModalityID = csn.ModalityID) else 
			M.departmentID end
			JOIN ADW.DIM.Period P
			ON IGY.DOS = P.[Date]
			JOIN ADW.dim.orgunit o
ON o.orgunitid=CSN.orgunitid
			LEFT JOIN ADW.dim.v_CurrentAsset CA
			ON CA.UnitNbr = IGY.Unit

			WHERE CSN.Is_Current = 1 
			) SUBQUERY
			group by Region,Company,DepartmentCode,ModalityID,CustomerNbr,Asset_id,Servicedate,Date_ID,PostDate
			HAVING SUM(ISNULL([retail_scan_count],0)) <> 0)INTERGYOUTPUT



INSERT INTO ADS.STG.Staging_ScanSummary
SELECT 
				   Region,
				   Company,
				   DepartmentCode,
				   ModalityID,
				   CustomerNbr,
				   Asset_id,
				   Servicedate,
				   Date_ID,
				   PostDate,
				   '0' AS [retail_scan_count],
				   WholeSaleScanCount,
				   WBScanCount,
				   WholeSaleScanCount AS Nbrscans,
				   'Wholesale' as Loadsource
FROM (
SELECT
					Region,
					Company,
					DepartmentCode,
					ModalityID,
					ServiceNbr AS CustomerNbr,
					UnitSegment AS Asset_id,
					Servicedate,
					Date_ID,
				PostDate,
SUM(WholeSaleScanCount) as WholeSaleScanCount
,SUM(WBScanCount)as WBScanCount
			
				FROM(
	  
	  
				select 
					OU.SubLatCode AS Region,
					C.CompanyCode AS Company,
					D.DepartmentCode,
					ISNull(M.ModalityID,CSN.ModalityID) AS ModalityID,
					SO.ServiceNbr,
					ISNULL(CA.UnitNbr,'0000') AS UnitSegment,
					SO.Servicedate,
					(SELECT P.DateID FROM ADW.dim.Period P WHERE P.[Date] = SO.ServiceDate) AS Date_ID,
				    (SELECT P.DateID FROM ADW.dim.Period P WHERE P.[Date] = SO.PostDate) AS PostDate,
                    SO.ScanCount as WholeSaleScanCount,
                    SO.WBScanCountas WBScanCount
				
			from [ADS].[dbo].[ScansODS] SO
			JOIN ADW.dim.CustomerServiceNumber CSN
			ON SO.ServiceNbr = CSN.CustomerServiceNumber
			JOIN ADW.DIM.OrgUnit OU
			ON CSN.OrgUnitID = OU.OrgUnitID
			join ADW.dim.Company C
			ON CSN.CompanyID = C.CompanyID
			LEFT join ADW.DIM.Modality M
			ON SO.ModalityCode =  M.ModalityCode
			JOIN ADW.dim.Department D
			ON D.DepartmentID = CASE WHEN M.departmentID is null then (select MO.departmentID from adw.dim.Modality MO where MO.ModalityID = csn.ModalityID) else 
			M.departmentID end
			JOin ADW.DIM.Period P
			ON SO.Servicedate = P.[date]
			LEFT JOIN ADW.dim.v_CurrentAsset CA
			ON CA.UnitNbr = SO.UnitSegment
			Where CSN.Is_Current = 1 AND
			SO.ScanType= 'Wholesale' 
			)SUBQUERY2

			group by Region,Company,DepartmentCode,ModalityID,ServiceNbr,UnitSegment,Servicedate,Date_ID,PostDate)SCANOUTPUT

END