IF (
@SiteCategoryId IN (
SELECT lookupid
FROM system_lookup
WHERE lookuptype = 'ctsitecategory'
AND lookupvalue IN (0)
)
)
BEGIN
INSERT INTO #temp (
RowNumber
,SiteCTId
,CTAppointmentDate
,CTType
,CTRemovalDate
,OM_SiteCT.SiteId
,ExtraCT
)
SELECT ROW_NUMBER() OVER (
PARTITION BY OM_SiteCT.SiteId
,ExtraCT ORDER BY SiteCTId
) AS 'RowNumber'
,SiteCTId
,CTAppointmentDate
,CTType
,CTRemovalDate
,OM_SiteCT.SiteId
,ExtraCT
FROM OM_SiteCT
,Site
,(
SELECT DISTINCT Siteid
FROM Share sh
WHERE (
(
(sh.ShareNo = 0)
AND (
(
SELECT SiteStatusId
FROM SIte
WHERE Siteid = sh.SiteId
) = @AnchorOnAirStatusId
AND (
SELECT AnchorOnAirDate
FROM SIte
WHERE Siteid = sh.SiteId
) <= GetDate()
)
)
OR (
(sh.ShareNo <> 0)
AND (
sh.ShareStatusId = @ShareOnAirStatusId
AND ShareOnAirDate IS NOT NULL
AND ShareOnAirDate <= GetDate()
)
)
)
) a
WHERE Site.SiteId = OM_SiteCT.SiteId
AND (
Site.CircleId IN (
SELECT value
FROM dbo.fn_split(@CircleId, ',')
)
OR @CircleId = '0'
)
--AND Site.CircleID = CASE WHEN @CircleId = 0 THEN Site.CircleId ELSE @CircleId END
AND Site.CircleId IN (
SELECT CircleId
FROM System_ContactCircles
WHERE ContactId = @ContactId
)
AND (
Site.EntityID IN (
SELECT value
FROM dbo.fn_split(@EntityId, ',')
)
OR @EntityId = '0'
)
--AND Site.EntityID = CASE WHEN @EntityId = 0 THEN Site.EntityId ELSE @EntityId END
AND (
(
(
OM_SiteCT.CTAppointmentDate BETWEEN @StartDT
AND @EndDT
)
OR (
ISNULL(OM_SiteCT.CTRemovalDate, GetDate()) BETWEEN @StartDT
AND @EndDT
)
)
OR (
(
@StartDT BETWEEN OM_SiteCT.CTAppointmentDate
AND ISNULL(OM_SiteCT.CTRemovalDate, GetDate())
)
OR (
@EndDT BETWEEN OM_SiteCT.CTAppointmentDate
AND ISNULL(OM_SiteCT.CTRemovalDate, GetDate())
)
)
)
AND OM_SiteCT.CTRemovalDate IS NOT NULL
AND site.siteid = a.siteid
END
END
CREATE NONCLUSTERED INDEX #IDX_Temp ON #temp (
SiteId
,SiteCTId
,ExtraCT
) INCLUDE (
CTAppointmentDate
,CTType
,CTRemovalDate
)
DECLARE @RowNumber INT
DECLARE @SiteCTId INT
DECLARE @CTAppointmentDate DATETIME
DECLARE @CTType VARCHAR(50)
DECLARE @CTRemovalDate DATETIME
DECLARE @SiteId INT
DECLARE @ExtraCT BIT
DECLARE @baseValue INT
DECLARE @CTCount INT
DECLARE @ExtraCTCount INT
DECLARE @MaxCount INT
DECLARE @SitesCount INT
DECLARE @previousSiteId INT
SET @previousSiteId = 0
DECLARE @OddRow INT
DECLARE @rowId INT
SET @OddRow = 0
SET @rowId = 0
CREATE NONCLUSTERED INDEX #IDX_Temp_CT ON #temp_CT (
SiteID
,SId
)
DECLARE Cur_SiteCT CURSOR
FOR
SELECT RowNumber
,SiteCTId
,CTAppointmentDate
,CTType
,CTRemovalDate
,SiteId
,ExtraCT
FROM #temp
ORDER BY SiteId
,RowNumber
OPEN Cur_SiteCT
FETCH Cur_SiteCT
INTO @RowNumber
,@SiteCTId
,@CTAppointmentDate
,@CTType
,@CTRemovalDate
,@SiteId
,@ExtraCT
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@previousSiteId <> @SiteId)
BEGIN
SET @CTCount = CASE
WHEN (
SELECT Count(*)
FROM #temp
WHERE ExtraCT = 0
AND SiteId = @SiteId
) = 0
THEN 1
ELSE (
SELECT Count(*)
FROM #temp
WHERE ExtraCT = 0
AND SiteId = @SiteId
)
END
SET @ExtraCTCount = CASE
WHEN (
SELECT Count(*)
FROM #temp
WHERE ExtraCT = 1
AND SiteId = @SiteId
) = 0
THEN 1
ELSE (
SELECT Count(*)
FROM #temp
WHERE ExtraCT = 1
AND SiteId = @SiteId
)
END
SET @MaxCount = (
SELECT CASE
WHEN (
@CTCount = 1
AND @ExtraCTCount = 1
)
THEN 1
ELSE CASE
WHEN @CTCount > @ExtraCTCount
THEN (
CASE
WHEN @CTCount % 2 = 0
THEN @CTCount / 2
ELSE (@CTCount / 2) + 1
END
)
ELSE (
CASE
WHEN @ExtraCTCount % 2 = 0
THEN @ExtraCTCount / 2
ELSE (@ExtraCTCount) + 1
END
)
END
END
)
SET @baseValue = 0
SET @OddRow = 0
SET @rowId = 0
WHILE (@baseValue < @MaxCount)
BEGIN
INSERT INTO #temp_CT (
SId
,SiteId
)
SELECT @baseValue + 1
,SiteId
FROM #temp
WHERE #temp.SiteId = @SiteId
GROUP BY #temp.SiteId
SET @baseValue = @baseValue + 1
END
END
SET @previousSiteId = @SiteId
IF (@RowNumber % 2 <> 0)
BEGIN
IF (@RowNumber <> @OddRow)
BEGIN
SET @OddRow = @RowNumber
SET @rowId = @rowId + 1
END
END
ELSE
SET @rowId = @rowId
UPDATE #temp
SET sId = @rowId
WHERE SiteCTId = @SiteCTId
AND SiteId = @SiteId
UPDATE #temp_CT
SET [Site ID] = Site.SiteRef
,[Site Name] = Site.SiteName
,[Circle] = System_Circle.CircleName
,[Entity] = System_Entity.EntityName
,[Site Type (GBT/RTT)] = (
SELECT lookuptext
FROM system_lookup
WHERE lookupId = Site.TowerTypeId
AND lookupType = 'TowerType'
) --dbo.GetLookupText(Site.TowerTypeId)
FROM Site
INNER JOIN #temp_CT ON #temp_CT.SiteID = Site.SiteID
LEFT OUTER JOIN System_Circle ON System_Circle.CircleId = Site.CircleId
LEFT OUTER JOIN System_Entity ON System_Entity.EntityId = Site.EntityId
WHERE Site.SiteId = @SiteId
IF (@RowNumber % 2 <> 0)
BEGIN
UPDATE #temp_CT
SET [CT Appointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
,[CT Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
SELECT lookuptext
FROM system_lookup
WHERE lookupId = CTType
AND lookupType = 'CTType'
), '')
,--ISNULL(dbo.GetLookupText(CTType),''),
[CT Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
FROM #temp
WHERE #temp.SiteId = @SiteId
AND #temp.ExtraCT = 0
AND #temp.RowNumber = @RowNumber
AND #temp.sId = #temp_CT.SId
AND #temp_CT.SiteID = #temp.SiteID
UPDATE #temp_CT
SET [Extra CT Appointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
,[Extra CT Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
SELECT lookuptext
FROM system_lookup
WHERE lookupId = CTType
AND lookupType = 'CTType'
), '')
,--ISNULL(dbo.GetLookupText(CTType),''),
[Extra CT Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
FROM #temp
WHERE #temp.SiteId = @SiteId
AND #temp.ExtraCT = 1
AND #temp.RowNumber = @RowNumber
AND #temp.sId = #temp_CT.SId
AND #temp_CT.SiteID = #temp.SiteID
END
ELSE
IF (@RowNumber % 2 = 0)
BEGIN
UPDATE #temp_CT
SET [CT Reappointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
,[CT Reappointment Type (Vendor/Owner/Relative/Patrolling)] = ISNULL((
SELECT lookuptext
FROM system_lookup
WHERE lookupId = CTType
AND lookupType = 'CTType'
), '')
,--ISNULL(dbo.GetLookupText(CTType),''),
[CT Reappointment Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
FROM #temp
WHERE #temp.SiteId = @SiteId
AND #temp.ExtraCT = 0
AND #temp.RowNumber = @RowNumber
AND #temp.sId = #temp_CT.SId
AND #temp_CT.SiteID = #temp.SiteID
UPDATE #temp_CT
SET [Extra CT Reappointment Date] = Convert(DATETIME, CTAppointmentDate, 103)
,[Extra CT Reappointment Type(Vendor/Owner/Relative/Patrolling)] = ISNULL((
SELECT lookuptext
FROM system_lookup
WHERE lookupId = CTType
AND lookupType = 'CTType'
), '')
,--ISNULL(dbo.GetLookupText(CTType),''),
[Extra CT Reappointment Removal Date] = Convert(DATETIME, CTRemovalDate, 103)
FROM #temp
WHERE #temp.SiteId = @SiteId
AND #temp.ExtraCT = 1
AND #temp.RowNumber = @RowNumber
AND #temp.sId = #temp_CT.SId
AND #temp_CT.SiteID = #temp.SiteID
END
UPDATE #temp_CT
SET [CT Status (Yes/No)] = dbo.GetCTStatus((
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
AND #temp.ExtraCT = 0
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
), (
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
AND #temp.ExtraCT = 1
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
))
,[Total No Of CT(Including Extra CT)] = dbo.GetTotalCTCount((
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
AND #temp.ExtraCT = 0
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
), (
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
AND #temp.ExtraCT = 1
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
))
,[Site Category] = (
SELECT lookuptext
FROM system_lookup
WHERE lookuptype = 'CTsitecategory'
AND lookupvalue = (
dbo.GetTotalCTCount((
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[CT Appointment Date]
AND #temp.ExtraCT = 0
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
), (
SELECT SiteCTId
FROM #temp
WHERE #temp.SiteId = #temp_CT.SiteId
AND #temp.CTAppointmentDate = #temp_CT.[Extra CT Appointment Date]
AND #temp.ExtraCT = 1
AND #temp.sId = #temp_CT.SId
AND #temp.RowNumber = @RowNumber
))
)
)
FETCH Cur_SiteCT
INTO @RowNumber
,@SiteCTId
,@CTAppointmentDate
,@CTType
,@CTRemovalDate
,@SiteId
,@ExtraCT
END
CLOSE Cur_SiteCT
DEALLOCATE Cur_SiteCT
IF (
@SiteCategoryId IN (
SELECT lookupid
FROM system_lookup
WHERE lookuptype = 'ctsitecategory'
AND lookupvalue IN (0)
)
)
BEGIN
--Select * from #temp Order By SiteId,RowNumber
SELECT [Site ID]
,[Site Name]
,[Circle]
,[Entity]
,[Site Type (GBT/RTT)]
,[CT Status (Yes/No)]
,[CT Appointment Date]
,[CT Type (Vendor/Owner/Relative/Patrolling)]
,[CT Removal Date]
,[Extra CT Appointment Date]
,[Extra CT Type (Vendor/Owner/Relative/Patrolling)]
,[Extra CT Removal Date]
,[CT Reappointment Date]
,[CT Reappointment Type (Vendor/Owner/Relative/Patrolling)]
,[CT Reappointment Removal Date]
,[Extra CT Reappointment Date]
,[Extra CT Reappointment Type(Vendor/Owner/Relative/Patrolling)]
,[Extra CT Reappointment Removal Date]
,[Total No Of CT(Including Extra CT)]
,[Site Category]
FROM #temp_CT
WHERE [Site Category] = 'CAT-C'
ORDER BY [Site ID]
END
END
What I have tried:
I tried to find out an alternative for cursor to optimize it.