Complete query
SET @vSelect =
'
SELECT
T1.BranchShortName AS BranchShortName,
(
SELECT TT1.EmployeeName
FROM [dbo].[Tb_OPL_Employee] AS TT1
JOIN [dbo].[Tb_MKT_SKD] AS TT2
ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
JOIN [dbo].[Tb_SYS_Approval] AS TT3
ON TT3.TaskId = TT2.TaskId
WHERE
TT3.IdTb_OPL_JobTitles = TT1.IdTb_OPL_JobTitles
AND TT3.IdTb_OPL_JobTitles =
(
SELECT MAX(TTT3.IdTb_OPL_JobTitles)
FROM [dbo].[Tb_SYS_Approval] AS TTT3
WHERE
TTT3.IsPassed = 1
AND TTT3.ApprovalLevelCode = ''Approve''
AND TTT3.TaskId =TT2.TaskId
)
AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
) AS LastApproval,
(
SELECT TT1.EmployeeName
FROM [dbo].[Tb_OPL_Employee] AS TT1
JOIN [dbo].[Tb_MKT_SKD] AS TT2
ON TT2.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
JOIN [dbo].[Tb_SYS_Approval] AS TT3
ON TT3.TaskId = TT2.TaskId
WHERE
TT1.IdTb_OPL_JobTitles = TT3.IdTb_OPL_JobTitles
AND TT3.IdTb_OPL_JobTitles =
(
SELECT MIN(TTT3.IdTb_OPL_JobTitles)
FROM [dbo].[Tb_SYS_Approval] AS TTT3
WHERE
TTT3.IsPassed = 0
AND TTT3.ApprovalLevelCode = ''Approve''
AND TTT3.TaskId =TT2.TaskId
)
AND TT3.IdTb_OPL_Branch = TT1.IdTb_OPL_Branch
AND TT2.IdTb_MKT_SKD = T2.IdTb_MKT_SKD
) AS NextApproval,
T6.StatusDescription AS Status
'
SET @vWhere = ' WHERE 1=1 '
IF (@ddlSearchCriteria = '1')
BEGIN
-- The condition of search textbox is not empty
IF (@txtSearch <> '')
BEGIN
-- The Condition of StartDate is not empty and EndDate is empty
IF (@txtStartDate <> '') AND (@txtEndDate = '')
SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)'
-- The Condition of StartDate is empty and EndDate is not empty
IF (@txtStartDate = '') AND (@txtEndDate <> '')
SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'
-- The Condition of StartDate is not empty and EndDate is not empty
IF (@txtStartDate <> '') AND (@txtEndDate <> '')
SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%''
AND T2.CreatedDate >= CONVERT(SMALLDATETIME, ''' + @txtStartDate + ''', 101)
AND T2.CreatedDate <= CONVERT(SMALLDATETIME, ''' + @txtEndDate + ''', 101)'
IF (@txtStartDate = '') AND (@txtEndDate = '')
SET @vWhere = @vWhere + ' AND T1.BranchShortName LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'''
PRINT @vSelect+@vWhere
END
How can I get the subquery value for LastApproval to compare like I do with T1.BranchShortName?
I tried changing it like:
... IF (@txtStartDate <> '') AND (@txtEndDate = '')
SET @vWhere = @vWhere + ' AND **T1.BranchShortName --> LastApproval** LIKE ''%' + ltrim(rtrim(@txtSearch)) + '%'' ...
but this gave me a message saying there was an error in my where clause.