SQLTeam.com | Weblogs | Forums

Avoiding too may temp tables & tweak the query

Hi, In the following I am using too may temp tables.
How to avoid those & tweak the query in a better way? Thanks in advance.

	    DECLARE @RecordCount int;
	
		DECLARE  @tempsectorId bigint, @tempmemberId bigint;

		-- sector panel data
		;WITH CTE AS(
		SELECT S.SectorID,V.TypeOfVisitID,count(V.TypeOfVisitID) as Counts  
		FROM VisitsMaster V 
		INNER JOIN MemberMaster M ON M.MemberID = V.MemberID
		INNER JOIN SectorMaster S ON S.SectorID = M.SectorID
		where ISNULL(V.IsDeleted,0) = 0
		Group by  S.SectorID,V.TypeOfVisitID
		),

		Header AS (
		SELECT S.SectorID, S.Name as SectorName, S.ImageURL as SectorImage, T.TypeOfVisitID,T.TypeOfVisit 
		FROM SectorMaster S CROSS JOIN TypeOfVisitsMaster T
		)

		SELECT
		T.SectorID,
		T.SectorName,
		T.SectorImage,
		T.TypeOfVisitID,
		(select TV.TypeOfVisit from TypeOfVisitsMaster TV where TV.TypeOfVisitID = T.TypeOfVisitID) as Type,
		ISNULL((select ST.Counts from CTE ST where ST.SectorID=T.SectorID AND ST.TypeOfVisitID= T.TypeOfVisitID ),0) as Counts,
		0 AS TotalBySector
		into #tempsectorsData
		from Header T order by T.SectorID,T.TypeOfVisitID;

		SELECT sectorID, SUM(Counts) as TotalBySector 
		into #tempTotalsectorsData
		from #tempsectorsData
		GROUP BY sectorID;

		UPDATE T
		SET T.TotalBySector = S.TotalBySector
		from #tempsectorsData T
		Inner JOIN #tempTotalsectorsData S ON T.sectorID = S.sectorID;

		SELECT 
		T.SectorID,
		T.SectorName,
		T.SectorImage,
		T.TypeOfVisitID,
		T.Type,
		T.Counts,
		T.TotalBySector,
		(Select count(*) from MemberMaster M Where (M.IsDeleted = 0) and (M.SectorID = T.SectorID)) as TotalMembersBySector,
		'VISITS' as DashboardType
		FROM #tempsectorsData T
		ORDER BY T.SectorID,T.TypeOfVisitID;
		
		SELECT @RecordCount= @@ROWCOUNT;

One Idea is

MERGE ...

is this some sort of query for reporting purposes? some sort of dashboard?

It looks like that can all be done in 1 query, no need to use temp tables. If you provide ddl and sample data, we could better help you