There is a text column that can (and is actually) taking advantage of a FT Index. I deployed and tested the change on our Dev Box. Ran old and new sproc (pretty much same logic, besides the CONTAINS) and now I get more rows with the FT Index.
Is that normal?
I honestly remember a similar improvement on a different sproc a few years ago but it seems the result set was consistent but can't remember.
let's see the old and new
Old sproc
CREATE PROCEDURE [dbo].[OLD]
@CompanyID int,
@MultiCompanyID int,
@Billable int,
@AssignedUserID int,
@QueueID int,
@TicketStatusID int,
@StartDate smalldatetime = null,
@EndDate smalldatetime = null,
@Keyword varchar(50) = '',
@ProjectID int = -1,
@TechID int = -1,
@OpenClosed int = 0,
@IssueID int =-1,
@CreatedByID int = -1,
@TicketName varchar(50) = '',
@FilteredKeyword varchar(50) = '',
@MinutesWorked int = null,
@SubscribedUserID int = -1,
@CPMStatus int = 0,
@TicketRecurOn BIT
AS
SET NOCOUNT ON
DECLARE
@iCompanyID int,
@iMultiCompanyID int,
@iBillable int,
@iAssignedUserID int,
@iQueueID int,
@iTicketStatusID int,
@iStartDate date,-- = null,
@iEndDate date,-- = null,
@iProjectID int,-- = -1,
@iTechID int,-- = -1,
@iKeyword varchar(52),
@iOpenClosed int = 3,
@iIssueID int,-- =-1,
@iCreatedByID int,-- = -1
@iTicketName varchar(50),
@iFilteredKeyword varchar(50),
@iMinutesWorked int,
@iSubscribedUserID int,
@iCPMStatus int
SET @iCompanyID = @CompanyID
SET @iMultiCompanyID = @MultiCompanyID
SET @iBillable = @Billable
SET @iAssignedUserID = @AssignedUserID
SET @iQueueID = @QueueID
SET @iTicketStatusID = @TicketStatusID
SET @iStartDate = Cast(@StartDate as Date)
SET @iEndDate = Cast(@EndDate as Date)
SET @iProjectID = @ProjectID
SET @iTechID = @TechID
SET @iIssueID = @IssueID
SET @iOpenClosed = @OpenClosed
SET @iCreatedByID = @CreatedByID
SET @iTicketName = @TicketName
SET @iFilteredKeyword = @FilteredKeyword
SET @iMinutesWorked = @MinutesWorked
SET @iSubscribedUserID = @SubscribedUserID
SET @iCPMStatus = @CPMStatus
DECLARE @TextKeyword varchar(54)
DECLARE @ResetSearch bit
If LTRIM(RTRIM(@Keyword)) <> ''
BEGIN
SET @iKeyword = '%' + LTRIM(RTRIM(@Keyword)) + '%'
SET @TextKeyword = '"' + LTRIM(RTRIM(@Keyword)) + '"'
Set @ResetSearch = 1
END
ELSE
BEGIN
SET @iKeyword = '%' + LTRIM(RTRIM(@iFilteredKeyword)) + '%'
SET @TextKeyword = '"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"'
Set @ResetSearch = 0
END
SET @iTicketName = LTRIM(RTRIM(@iTicketName))
DECLARE @temp TABLE
(
ID INT PRIMARY KEY IDENTITY(1,1),
TID INT,
UNIQUE CLUSTERED (ID,TID)
)
DECLARE @Results TABLE
(
ID INT PRIMARY KEY IDENTITY(1,1),
TID INT,
UNIQUE CLUSTERED (ID,TID)
)
If @iTicketName <> ''
BEGIN
--Only Search TicketName
INSERT INTO @Results (TID)
SELECT TicketID
FROM Ticket
WHERE (Name = @iTicketName)
AND DateDeactivated is null
GROUP BY TicketID
END
ELSE If @ResetSearch = 1
BEGIN
--Only Search for the keywords
INSERT INTO @Results (TID)
SELECT TicketID
FROM Entry
WHERE Notes like @iKeyword
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO @Results (TID)
SELECT TicketID
FROM Note
WHERE Note like @iKeyword
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO @Results (TID)
SELECT TicketID
FROM Ticket
WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
AND DateDeactivated is null
GROUP BY TicketID
End
ELSE If @iSubscribedUserID <> -1
BEGIN
--Only Search for Subscribed tickets
INSERT INTO @Results (TID)
SELECT t.TicketID
FROM Ticket t
INNER JOIN TicketSubscriber ts on t.TicketID = ts.TicketID AND ts.UserID = @iSubscribedUserID
WHERE DateDeactivated is null AND t.TicketStatusID <> 8
GROUP BY t.TicketID
End
ELSE If @iCPMStatus <> 0
BEGIN
--Only Search for ticket that have CPM status "pending review" or "reviewed"
INSERT INTO @Results (TID)
SELECT t.TicketID
FROM Ticket t
WHERE DateDeactivated is null
AND t.CPMReviewStatus = @CPMStatus
GROUP BY t.TicketID
End
ELSE IF @TicketRecurOn = 1
BEGIN
INSERT INTO @ResultS(TID)
SELECT T.TicketID
FROM Ticket T
INNER JOIN TicketRecur TR ON T.TicketID = TR.SourceTicketID
WHERE T.DateDeactivated IS NULL AND T.TicketStatusID = @iTicketStatusID
GROUP BY T.TicketID
END
Else
Begin
--Filter the Tickets first
Insert into @temp (TID)
Select T.TicketID
From Ticket T
INNER JOIN Company C on C.CompanyID = T.CompanyID
Left Join @temp KeywordTicket on T.TicketID = KeywordTicket.TID
WHERE
((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID))
AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID))
AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0)))
AND ((@iAssignedUserID=-1)
OR ((@iAssignedUserID=0) AND (T.AssignedUserID is null))
OR ((@iAssignedUserID=-2) AND (T.AssignedUserID is not null))
OR (T.AssignedUserID = @iAssignedUserID))
AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID))
AND ((@iTechID=-1) OR (T.TechID = @iTechID))
AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID))
AND (((@iTicketStatusID=-1) AND (T.TicketStatusID <> 8)) OR (@iTicketStatusID=-2) OR (T.TicketStatusID = @iTicketStatusID))
AND T.DateDeactivated is null
AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
Group By T.TicketID
--Backup Users Assigned
Insert into @temp (TID)
Select T.TicketID
From Ticket T
INNER JOIN TicketBackupUser TBU on TBU.TicketID = T.TicketID and TBU.UserID = @iAssignedUserID
INNER JOIN Company C on C.CompanyID = T.CompanyID
WHERE
((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID))
AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID))
AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0)))
AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID))
AND ((@iTechID=-1) OR (T.TechID = @iTechID))
AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID))
-- ONLY SHOW NEW TICKETS
AND (T.TicketStatusID=1)
AND T.DateDeactivated is null
AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
Group By T.TicketID
IF @iKeyword <> '%%'
BEGIN
INSERT INTO @Results (TID)
SELECT TicketID
FROM [Entry]
INNER JOIN @temp T on T.TID = [Entry].TicketID
WHERE Notes like @iKeyword
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO @Results (TID)
SELECT TicketID
FROM Note
INNER JOIN @temp T on T.TID = Note.TicketID
WHERE Note like @iKeyword
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO @Results (TID)
SELECT TicketID
FROM Ticket
INNER JOIN @temp T on T.TID = Ticket.TicketID
WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
AND DateDeactivated is null
GROUP BY TicketID
END
Else
BEGIN
INSERT INTO @Results (TID)
SELECT TID
From @temp
END
END
DECLARE @ResultsUnique TABLE(TID INT PRIMARY KEY)
INSERT INTO @ResultsUnique
SELECT DISTINCT TID FROM @Results
SELECT T.*,
TS.Name as TicketStatusName, TS.SortOrder as TicketStatusSortOrder,
C.Name as CompanyName,
ISNULL(MC.Name, 'Ctuit') as MultiCompanyName,
ISNULL(U.DisplayName, '(Unassigned)') as AssignedUser,
Q.Name as QueueName,
CASE WHEN ISNULL(T.PublicTicket, 0) = 0 or t.Ranking is null THEN TP.Name ELSE ISNULL(CONVERT(VARCHAR, t.Ranking),'') + ' - ' + TP.Name END as PriorityName,
ISNULL(U_tech.DisplayName,'') as TechName,
"HoursWorked" = ISNULL((SELECT SUM(ISNULL(dbo.fnSafeDiv(((ceiling(DATEDIFF(n, StartTime, EndTime)/5.0))*5.0), 60.0), 0))
FROM Entry E
WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0),
"IsBackup" = 0,
"OtherBillableTickets" = (SELECT COUNT(*)
FROM Ticket iT
WHERE iT.TicketStatusID <> 8
and iT.DateDeactivated is null
and iT.CompanyID = T.CompanyID
AND iT.Billable=1
and iT.TicketID <> T.TicketID)
,C.SupportType
,"MajorSort" = CASE WHEN T.IsClientDriven = 1 THEN 1 ELSE 2 END
FROM @ResultsUnique SelectedTickets
INNER JOIN Ticket T on T.TicketID = SelectedTickets.TID
INNER JOIN Company C on C.CompanyID = T.CompanyID
LEFT JOIN MultiCompany MC on C.MultiCompanyID = MC.MultiCompanyID
INNER JOIN TicketStatus TS on TS.TicketStatusID = T.TicketStatusID
INNER JOIN TicketPriority TP on TP.TicketPriorityID = T.TicketPriorityID
LEFT JOIN Users U on U.UserID = T.AssignedUserID
INNER JOIN Queue Q on Q.QueueID = T.QueueID
LEFT JOIN Users U_tech on U_tech.UserID = T.TechID
WHERE
((@iMinutesWorked is null) OR (ISNULL((SELECT SUM(ISNULL(DATEDIFF(n, StartTime, EndTime), 0))
FROM Entry E
WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0) >= (@iMinutesWorked)))
ORDER BY T.IsClientDriven DESC, T.Name DESC
New version
ALTER PROCEDURE [dbo].[NEW]
@CompanyID int,
@MultiCompanyID int,
@Billable int,
@AssignedUserID int,
@QueueID int,
@TicketStatusID int,
@StartDate smalldatetime = null,
@EndDate smalldatetime = null,
@Keyword varchar(50) = '',
@ProjectID int = -1,
@TechID int = -1,
@OpenClosed int = 0,
@IssueID int =-1,
@CreatedByID int = -1,
@TicketName varchar(50) = '',
@FilteredKeyword varchar(50) = '',
@MinutesWorked int = null,
@SubscribedUserID int = -1,
@CPMStatus int = 0,
@TicketRecurOn BIT
AS
SET NOCOUNT ON
DECLARE
@iCompanyID int,
@iMultiCompanyID int,
@iBillable int,
@iAssignedUserID int,
@iQueueID int,
@iTicketStatusID int,
@iStartDate date,-- = null,
@iEndDate date,-- = null,
@iProjectID int,-- = -1,
@iTechID int,-- = -1,
@iKeyword varchar(52),
@iOpenClosed int = 3,
@iIssueID int,-- =-1,
@iCreatedByID int,-- = -1
@iTicketName varchar(50),
@iFilteredKeyword varchar(50),
@iMinutesWorked int,
@iSubscribedUserID int,
@iCPMStatus int
SET @iCompanyID = @CompanyID
SET @iMultiCompanyID = @MultiCompanyID
SET @iBillable = @Billable
SET @iAssignedUserID = @AssignedUserID
SET @iQueueID = @QueueID
SET @iTicketStatusID = @TicketStatusID
SET @iStartDate = Cast(@StartDate as Date)
SET @iEndDate = Cast(@EndDate as Date)
SET @iProjectID = @ProjectID
SET @iTechID = @TechID
SET @iIssueID = @IssueID
SET @iOpenClosed = @OpenClosed
SET @iCreatedByID = @CreatedByID
SET @iTicketName = @TicketName
SET @iFilteredKeyword = @FilteredKeyword
SET @iMinutesWorked = @MinutesWorked
SET @iSubscribedUserID = @SubscribedUserID
SET @iCPMStatus = @CPMStatus
DECLARE @TextKeyword varchar(54)
DECLARE @ResetSearch bit
If LTRIM(RTRIM(@Keyword)) <> ''
BEGIN
--SET @iKeyword = '%' + LTRIM(RTRIM(@Keyword)) + '%'
SET @iKeyword = '"%"' + LTRIM(RTRIM(@Keyword)) + '"%"'
SET @TextKeyword = '"' + LTRIM(RTRIM(@Keyword)) + '"'
Set @ResetSearch = 1
END
ELSE
BEGIN
SET @iKeyword = '"%"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"%"'
SET @TextKeyword = '"' + LTRIM(RTRIM(@iFilteredKeyword)) + '"'
Set @ResetSearch = 0
END
SET @iTicketName = LTRIM(RTRIM(@iTicketName))
--CB: If the user included the quotes already then replace the doubled double quote with single double quote
SET @TextKeyword = Replace(@TextKeyword,'""','"')
--CB: The user will be including quotes in the string, so it will break iKeyword, replacing those out.
SET @iKeyword = Replace(@iKeyword,'"','')
CREATE TABLE #temp
(
ID INT PRIMARY KEY IDENTITY(1,1),
TID INT,
UNIQUE CLUSTERED (ID,TID)
)
--DECLARE @Results TABLE
-- (
-- ID INT PRIMARY KEY IDENTITY(1,1),
-- TID INT,
-- UNIQUE CLUSTERED (ID,TID)
-- )
IF object_id('tempdb.dbo.#Results', 'u') IS NOT NULL
DROP TABLE #Results
CREATE TABLE #Results
(
ID INT IDENTITY(1,1),
TID INT
)
If @iTicketName <> ''
BEGIN
--Only Search TicketName
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.Ticket
WHERE Name = @iTicketName
AND DateDeactivated is null
GROUP BY TicketID
END
ELSE If @ResetSearch = 1
BEGIN
--Only Search for the keywords
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.[Entry]
WHERE CONTAINS(Notes,@TextKeyword)
--WHERE Notes like @iKeyword
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.Note
--WHERE Note like @iKeyword
WHERE CONTAINS(Note, @TextKeyword)
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.Ticket
--Using the text keyword in all contains clauses. The likeness is handled there.
WHERE (Name like @iKeyword OR CONTAINS(Title,@TextKeyword) OR CONTAINS(Description,@TextKeyword) )
AND DateDeactivated is null
GROUP BY TicketID
End
ELSE If @iSubscribedUserID <> -1
BEGIN
--Only Search for Subscribed tickets
INSERT INTO #Results (TID)
SELECT t.TicketID
FROM dbo.Ticket t
INNER JOIN dbo.TicketSubscriber ts on t.TicketID = ts.TicketID AND ts.UserID = @iSubscribedUserID
WHERE DateDeactivated is null AND t.TicketStatusID <> 8
GROUP BY t.TicketID
End
ELSE If @iCPMStatus <> 0
BEGIN
--Only Search for ticket that have CPM status "pending review" or "reviewed"
INSERT INTO #Results (TID)
SELECT t.TicketID
FROM dbo.Ticket t
WHERE DateDeactivated is null
AND t.CPMReviewStatus = @CPMStatus
GROUP BY t.TicketID
End
ELSE IF @TicketRecurOn = 1
BEGIN
INSERT INTO #ResultS(TID)
SELECT T.TicketID
FROM Ticket T
INNER JOIN TicketRecur TR ON T.TicketID = TR.SourceTicketID
WHERE T.DateDeactivated IS NULL AND T.TicketStatusID = @iTicketStatusID
GROUP BY T.TicketID
END
Else
Begin
--Filter the Tickets first
Insert into #temp (TID)
Select T.TicketID
From dbo.Ticket T
INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
Left Join #temp KeywordTicket on T.TicketID = KeywordTicket.TID
WHERE
((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID))
AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID))
AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0)))
AND ((@iAssignedUserID=-1)
OR ((@iAssignedUserID=0) AND (T.AssignedUserID is null))
OR ((@iAssignedUserID=-2) AND (T.AssignedUserID is not null))
OR (T.AssignedUserID = @iAssignedUserID))
AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID))
AND ((@iTechID=-1) OR (T.TechID = @iTechID))
AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID))
AND (((@iTicketStatusID=-1) AND (T.TicketStatusID <> 8)) OR (@iTicketStatusID=-2) OR (T.TicketStatusID = @iTicketStatusID))
AND T.DateDeactivated is null
AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
Group By T.TicketID
--Backup Users Assigned
Insert into #temp (TID)
Select T.TicketID
From dbo.Ticket T
INNER JOIN dbo.TicketBackupUser TBU on TBU.TicketID = T.TicketID and TBU.UserID = @iAssignedUserID
INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
WHERE
((@iCompanyID=-1) OR (T.CompanyID = @iCompanyID))
AND ((@iMultiCompanyID=-1) OR (C.MultiCompanyID = @iMultiCompanyID))
AND ((@iBillable=-1) OR (T.Billable = @iBillable) OR ((@iBillable=2) AND (T.Billable=1) AND (T.Billed=0)))
AND ((@iQueueID=-1 AND T.QueueID <> 37) OR (T.QueueID = @iQueueID))
AND ((@iTechID=-1) OR (T.TechID = @iTechID))
AND ((@iProjectID=-1) OR (T.ProjectID = @iProjectID))
-- ONLY SHOW NEW TICKETS
AND (T.TicketStatusID=1)
AND T.DateDeactivated is null
AND ((@iStartDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated >= @iStartDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate >= @iStartDate)) OR ((@iOpenClosed=2) AND (T.DueDate >= @iStartDate)) )
AND ((@iEndDate is null) OR ((@iOpenClosed=0) AND (T.DateActivated <= @iEndDate)) OR ((@iOpenClosed=1) AND (T.CompletedDate <= @iEndDate)) OR ((@iOpenClosed=2) AND (T.DueDate <= @iEndDate)))
AND ((@iIssueID=-1) OR (T.IssueID=@iIssueID))
AND ((@iCreatedByID = -1) OR (T.CreatedByUserID = @iCreatedByID))
Group By T.TicketID
IF @iKeyword <> '%%'
BEGIN
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.[Entry]
INNER JOIN #temp T on T.TID = [Entry].TicketID
--WHERE Notes like @iKeyword
WHERE CONTAINS(Notes,@TextKeyword)
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.Note
INNER JOIN #temp T on T.TID = Note.TicketID
--WHERE Note like @iKeyword
WHERE CONTAINS(Note, @TextKeyword)
AND DateDeactivated is null
GROUP BY TicketID
INSERT INTO #Results (TID)
SELECT TicketID
FROM dbo.Ticket
INNER JOIN #temp T on T.TID = Ticket.TicketID
--WHERE (Name like @iKeyword OR Title like @iKeyword OR CONTAINS(Description,@TextKeyword) )
WHERE (Name like @iKeyword OR CONTAINS(Title, @TextKeyword) OR CONTAINS(Description,@TextKeyword) )
AND DateDeactivated is null
GROUP BY TicketID
END
Else
BEGIN
INSERT INTO #Results (TID)
SELECT TID
From #temp
END
END
--DECLARE @ResultsUnique TABLE(TID INT PRIMARY KEY)
IF object_id('tempdb.dbo.#ResultsUnique', 'u') IS NOT NULL
DROP TABLE #ResultsUnique
CREATE TABLE #ResultsUnique
(TID INT)
INSERT INTO #ResultsUnique
SELECT DISTINCT TID FROM #Results
SELECT T.TicketID, T.Name, T.CompanyID, T.ContactID, T.Title, T.[Description], T.QueueID, T.AssignedUserID, T.TicketPriorityID, T.TicketStatusID, T.DueDate, T.IssueID, T.SubIssueID, T.Billable, T.QuotedPrice,
T.QuotedHour, T.AuthorizedByContactID, T.Billed, T.DateActivated, T.DateDeactivated, T.TicketSourceID, T.CreatedByUserID, T.LastModifiedByUserID, T.CompletedDate, T.ProjectID, T.TechID, T.AmountBilled,
T.BilledCode, T.BilledNotes, T.Comped, T.CompReason, T.CompAmount, T.Ranking, T.PublicTicket, T.DateApproved, T.BillingCompanyID, T.InvoiceNumber, T.DeveloperID, T.IsClientDriven, T.CPMReviewStatus,
T.CPMReviewDate, T.CPMReviewUserID,
TS.Name as TicketStatusName, TS.SortOrder as TicketStatusSortOrder,
C.Name as CompanyName,
ISNULL(MC.Name, 'Ctuit') as MultiCompanyName,
ISNULL(U.DisplayName, '(Unassigned)') as AssignedUser,
Q.Name as QueueName,
CASE WHEN ISNULL(T.PublicTicket, 0) = 0 or t.Ranking is null THEN TP.Name ELSE ISNULL(CONVERT(VARCHAR, t.Ranking),'') + ' - ' + TP.Name END as PriorityName,
ISNULL(U_tech.DisplayName,'') as TechName,
"HoursWorked" = ISNULL(HoursWorked.HoursWorked, 0),
"IsBackup" = 0,
"OtherBillableTickets" = OtherBillableTickets.OtherBillableTickets
,C.SupportType
,"MajorSort" = CASE WHEN T.IsClientDriven = 1 THEN 1 ELSE 2 END
FROM #ResultsUnique SelectedTickets
INNER JOIN dbo.Ticket T on T.TicketID = SelectedTickets.TID
INNER JOIN dbo.Company C on C.CompanyID = T.CompanyID
LEFT JOIN dbo.MultiCompany MC on C.MultiCompanyID = MC.MultiCompanyID
INNER JOIN dbo.TicketStatus TS on TS.TicketStatusID = T.TicketStatusID
INNER JOIN dbo.TicketPriority TP on TP.TicketPriorityID = T.TicketPriorityID
LEFT JOIN dbo.Users U on U.UserID = T.AssignedUserID
CROSS APPLY (SELECT COUNT(*) OtherBillableTickets
FROM dbo.Ticket iT
WHERE iT.TicketStatusID <> 8
and iT.DateDeactivated is null
and iT.CompanyID = T.CompanyID
AND iT.Billable=1
and iT.TicketID <> T.TicketID) OtherBillableTickets
INNER JOIN dbo.[Queue] Q on Q.QueueID = T.QueueID
LEFT JOIN dbo.Users U_tech on U_tech.UserID = T.TechID
OUTER APPLY (SELECT SUM(ISNULL(CASE WHEN 60.0 = 0 THEN 0 ELSE ((ceiling(DATEDIFF(n, StartTime, EndTime)/5.0))*5.0) / 60.0 END, 0)) HoursWorked
FROM dbo.[Entry] E
WHERE E.TicketID = T.TicketID and E.DateDeactivated is null) HoursWorked
WHERE
((@iMinutesWorked is null) OR (ISNULL((SELECT SUM(ISNULL(DATEDIFF(n, StartTime, EndTime), 0))
FROM dbo.[Entry] E
WHERE E.TicketID = T.TicketID and E.DateDeactivated is null), 0) >= (@iMinutesWorked)))
ORDER BY T.IsClientDriven DESC, T.Name DESC
DROP TABLE #Results
DROP TABLE #ResultsUnique
DROP TABLE #tempIn your final SELECTs, the Old and New are not the same. The New one has extra outer joins (2 of them IIUC) which can cause more rows selected
Thanks! I will take a look. I also discovered there were changes (not informed to me) that are not included on new one. That may case a discrepancy too.