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;