I have a stored procedure that is working perfectly but the issue is performance. There are so many joins in this stored procedure and unions... So after having the query executed, i need to send only 50 items to the application but as per my modification, it's taking the same time for 50 items or more... So can we tune it to reduce the time? I have done indexing and all but still its taking 12-15 secs for 3000 records.
ALTER PROCEDURE [dbo].[spGetForemanReportsCustomPaging]
@OFficeStaffID INT = NULL,
@SupervisorID INT = NULL,
@CustomerID INT = NULL,
@ContractAreaID INT = NULL,
@JobID INT = NULL,
@JobDescription NVARCHAR(500) = NULL,
@CustomerSupervisorID INT = NULL,
@ForemanID INT = NULL,
@StartDate DATETIME = NULL,
@EndDate DATETIME = NULL,
@PageIndex INT = 0,
@PageSize INT = 50,
@SortExpression nvarchar(50),
@SortDirection nvarchar(50)
AS
BEGIN
Declare @StartRowIndex int
Declare @EndRowIndex int
Set @StartRowIndex = (@PageIndex * @PageSize) + 1;
Set @EndRowIndex = (@PageIndex + 1) * @PageSize;
DECLARE @IsSupportServiceSupervisor tinyInt = null
SET @IsSupportServiceSupervisor = (SELECT COUNT(*) FROM tblEmployeeToAdditionalRole INNER JOIN tblUser ON tblEmployeeToAdditionalRole.EmployeeID = tblUser.ID
INNER JOIN tblAdditionalRoles on tblAdditionalRoles.ID = tblEmployeeToAdditionalRole.AdditionalRoleID
WHERE tblUser.ID = @SupervisorID and tblAdditionalRoles.Role = 'Support Service Supervisor');
--Added by Rishi on 22-Nov-2016 to show all records related to the selected foreman and his all jobs...
DECLARE @JobIDs TABLE(val INT)
INSERT INTO @JobIDs
SELECT DISTINCT JobID FROM tblForemanReports
WHERE tblForemanReports.ForemanID = @ForemanID
UNION
SELECT DISTINCT JobID FROM tblSubContractorForm
WHERE tblSubContractorForm.CreatedBy = (SELECT LowerUserName from tblUser Where ID = @ForemanID)
UNION
SELECT DISTINCT JobID FROM tblFieldSupportServices
WHERE tblFieldSupportServices.CreatedBy = (SELECT LowerUserName from tblUser Where ID = @ForemanID)
UNION
SELECT DISTINCT JobID FROM tblPavingDetails
INNER JOIN tblPavingOnlyForm ON tblPavingDetails.PavingOnlyFormID = tblPavingOnlyForm.ID
WHERE tblPavingOnlyForm.ForemanID = @ForemanID
UPDATE tblProbablePayItems SET Total = 0 WHERE Total IS NULL OR Total = ''
UPDATE tblProbableChangeOrders SET Total = 0 WHERE Total IS NULL OR Total = ''
UPDATE tblJobCostingSide SET Amount = 0 WHERE Amount IS NULL
SELECT * FROM
(SELECT ROW_NUMBER() over
(
order by
case when @SortExpression = 'JobID' and @SortDirection = 'ASC'
then ActiveForemanReports.JobID end asc,
case when @SortExpression = 'JobID' and @SortDirection = 'DESC'
then ActiveForemanReports.JobID end desc,
case when @SortExpression = 'JobDescription' and @SortDirection = 'ASC'
then ActiveForemanReports.JobDescription end asc,
case when @SortExpression = 'JobDescription' and @SortDirection = 'DESC'
then ActiveForemanReports.JobDescription end desc,
case when @SortExpression = 'WorkDate' and @SortDirection = 'ASC'
then ActiveForemanReports.WorkDate end asc,
case when @SortExpression = 'WorkDate' and @SortDirection = 'DESC'
then ActiveForemanReports.WorkDate end desc,
case when @SortExpression = 'Foreman' and @SortDirection = 'ASC'
then ActiveForemanReports.Foreman end asc,
case when @SortExpression = 'Foreman' and @SortDirection = 'DESC'
then ActiveForemanReports.Foreman end desc,
case when @SortExpression = 'FormType' and @SortDirection = 'ASC'
then ActiveForemanReports.FormType end asc,
case when @SortExpression = 'FormType' and @SortDirection = 'DESC'
then ActiveForemanReports.FormType end desc,
--case when @SortExpression = 'UnitilWorkSheetID' and @SortDirection = 'ASC'
-- then finalPayToInvoice.WorkSheet end asc,
--case when @SortExpression = 'UnitilWorkSheetID' and @SortDirection = 'DESC'
-- then finalPayToInvoice.WorkSheet end desc,
case when @SortExpression = 'ProbablePayItem' and @SortDirection = 'ASC'
then ActiveForemanReports.PayItem end asc,
case when @SortExpression = 'ProbablePayItem' and @SortDirection = 'DESC'
then ActiveForemanReports.PayItem end desc,
case when @SortExpression = 'ChangeOrder' and @SortDirection = 'ASC'
then ActiveForemanReports.ChangeOrder end asc,
case when @SortExpression = 'ChangeOrder' and @SortDirection = 'DESC'
then ActiveForemanReports.ChangeOrder end desc,
case when @SortExpression = 'TotalInvoiceAmount' and @SortDirection = 'ASC'
then ActiveForemanReports.InvoiceAmount end asc,
case when @SortExpression = 'TotalInvoiceAmount' and @SortDirection = 'DESC'
then ActiveForemanReports.InvoiceAmount end desc
) as RowNumber,
*,
dbo.GetProfitPercentage(ActiveForemanReports.PayItem, ActiveForemanReports.ChangeOrder, ActiveForemanReports.CostItem) AS Percentage FROM
(SELECT tblForemanReports.JobID, dbo.fnGetJobIDStringByJobId(tblForemanReports.JobID) AS JobIDString, tblForemanReports.ID AS ForemanReportID, tblForemanReports.ReviewDone, tblForemanReports.ReadyForInvoice,
tblForemanReports.AllWorkCompletedForJob, tblForemanReports.IsAllWorkDoneForProject, statusForeman.StatusName,
tblJobDetails.JobDescription,
tblCustomer.FullName As Customer,
tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Foreman Report' AS [FormType],
tblForemanReports.WorkDate,
tblForemanReports.PremiumTime,
CASE WHEN tblForemanReports.ContractTypeID = 2
THEN CAST(1 AS BIT)
ELSE
CAST(0 AS BIT)
END AS TandM,
((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblForemanReports.WorkDate
AND JobID = tblForemanReports.JobID
AND (ForemanReportID = tblForemanReports.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,
--((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbableChangeOrders
----INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
--WHERE WorkDate = tblForemanReports.WorkDate
--AND JobID = tblForemanReports.JobID
--AND (ForemanReportID = tblForemanReports.ID)
----AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
--AND InvoiceID IS NULL
--AND Total IS NOT NULL AND Total <> ''
--)) AS ChangeOrder,
NULLIF(((SELECT ISNULL(SUM((CAST(Total AS Decimal(20,2)))), 0) FROM tblProbableChangeOrders
--INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
WHERE WorkDate = tblForemanReports.WorkDate
AND JobID = tblForemanReports.JobID
AND (ForemanReportID = tblForemanReports.ID)
AND tblProbableChangeOrders.IsDeleted IS NULL
--AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
AND InvoiceID IS NULL
AND Total IS NOT NULL AND Total <> ''
) +
(SELECT ISNULL(SUM(Total), 0) FROM tblProbableChangeOfScope
INNER JOIN tblChangeOfScope ON tblChangeOfScope.ID = tblProbableChangeOfScope.ChangeOfScopeID
WHERE (tblProbableChangeOfScope.WorkDate = tblForemanReports.WorkDate)
AND (tblProbableChangeOfScope.JobID = tblForemanReports.JobID)
AND (tblChangeOfScope.ForemanID = tblForemanReports.ForemanID))),0) AS ChangeOrder,
--(SUM(CAST(tblJobCostingSide.Amount AS Decimal(20,2)))) AS CostItem,
((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblForemanReports.WorkDate
AND JobID = tblForemanReports.JobID
AND (tblJobCostingSide.ForemanReportID = tblForemanReports.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,
0.0 AS InvoiceAmount,
CASE
WHEN (SELECT COUNT(tblChangeOrderGenerated.ID)
FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates
ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
WHERE tblChangeOrderGenerated.JobID = tblForemanReports.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblForemanReports.WorkDate) > 0
THEN 1
ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType
FROM tblForemanReports
--Commented By Noor 12th May 2016 :- For Foreman report the foreman name will be the one selected on Foreman report form.
--LEFT JOIN tblUser ON LOWER(tblForemanReports.CreatedBy) = LOWER(tblUser.Username)
LEFT JOIN tblUser ON tblForemanReports.ForemanID = tblUser.ID
LEFT JOIN tblJobCostingSide ON (tblJobCostingSide.ForemanReportID = tblForemanReports.ID
AND tblJobCostingSide.Amount IS NOT NULL)
INNER JOIN tblJobDetails ON tblJobDetails.ID = tblForemanReports.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
INNER JOIN tblCustomer ON tblCustomer.ID = tblJobDetails.CustomerID
LEFT JOIN tblProbablePayItems ON tblProbablePayItems.ForemanReportID = tblForemanReports.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID
LEFT JOIN tblStatus statusForeman ON tblForemanReports.StatusID = statusForeman.ID
LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblForemanReports.JobToWorkOrderID
LEFT JOIN tblScatteredServices ON
(tblScatteredServices.JobID = tblJobDetails.ID
AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)
LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID
AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)
LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID
AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)
WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID
OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID
OR @OFficeStaffID IS NULL)
AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID
OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID
OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
OR @SupervisorID IS NULL)
AND (tblForemanReports.ReadyForInvoice IS NULL OR tblForemanReports.ReadyForInvoice = 0)
AND (tblProbablePayItems.InvoiceID IS NULL)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
OR @CustomerSupervisorID IS NULL)
--Commented By Noor 12th May 2016 :- Foreman reports will be filtered based on the foreman selected on FR and not the one who has created it.
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
--AND (tblForemanReports.ForemanID = @ForemanID OR @ForemanID is Null)
AND (tblForemanReports.WorkDate >= @StartDate OR @StartDate IS NULL)
AND (tblForemanReports.WorkDate <= @EndDate OR @EndDate IS NULL)
GROUP BY tblJobDetails.JobDescription,
tblCustomer.FullName,
tblForemanReports.ID,
tblForemanReports.ForemanID,
tblForemanReports.WorkDate,
tblForemanReports.PremiumTime,
tblForemanReports.ContractTypeID,
tblForemanReports.JobID,
tblUser.FullName,
tblUser.ID,
tblJobCostingSide.ForemanReportID, tblJobCostingSide.WorkDate,
tblJobCostingSide.FieldSupportServicesID,
tblJobCostingSide.SubcontractorFormID,
tblJobCostingSide.JobID,
tblForemanReports.ReviewDone, tblForemanReports.ReadyForInvoice,
tblForemanReports.AllWorkCompletedForJob,
tblForemanReports.IsAllWorkDoneForProject,
tblForemanReports.CreatedBy,
statusForeman.StatusName,
tblJobType.Title
UNION
SELECT tblPavingDetails.JobID, dbo.fnGetJobIDStringByJobId(tblPavingDetails.JobID) AS JobIDString, tblPavingDetails.ID AS PavingDetailID, tblPavingDetails.ReviewDone, tblPavingDetails.ReadyForInvoice,
'' AS [AllWorkCompletedForJob], '' AS [IsAllWorkDoneForProject], statusPavingOnly.StatusName,
tblJobDetails.JobDescription,
NULL AS Customer,
tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Paving Only' AS [FormType],
tblPavingOnlyForm.WorkDate,
NULL AS PremiumTime,
CAST(0 AS BIT) AS TandM,
((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblPavingOnlyForm.WorkDate
AND JobID = tblPavingDetails.JobID
AND (tblProbablePayItems.PavingDetailID = tblPavingDetails.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,
0.0 AS ChangeOrder,
((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblPavingOnlyForm.WorkDate
AND JobID = tblPavingDetails.JobID
AND (tblJobCostingSide.PavingOnlyFormID = tblPavingOnlyForm.ID)
AND (tblJobCostingSide.PavingDetailID = tblPavingDetails.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,
0.0 AS InvoiceAmount,
CASE
WHEN (SELECT COUNT(tblChangeOrderGenerated.ID)
FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates
ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
WHERE tblChangeOrderGenerated.JobID = tblPavingDetails.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblPavingOnlyForm.WorkDate) > 0
THEN 1
ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType
FROM tblPavingDetails
INNER JOIN tblPavingOnlyForm ON tblPavingOnlyForm.ID = tblPavingDetails.PavingOnlyFormID
--Replaced by Rishi on 8-Nov-2016 to filter Paving only records based on Foreman ID.
LEFT JOIN tblUser ON tblPavingOnlyForm.ForemanID = tblUser.ID
INNER JOIN tblJobDetails ON tblJobDetails.ID = tblPavingDetails.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
--LEFT JOIN tblForemanReports ON tblProbablePayItems.ForemanReportID = tblForemanReports.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID
LEFT JOIN tblStatus statusPavingOnly ON tblPavingOnlyForm.StatusID = statusPavingOnly.ID
LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblPavingDetails.WOID
LEFT JOIN tblScatteredServices ON
(tblScatteredServices.JobID = tblJobDetails.ID
AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)
LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID
AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)
LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID
AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)
WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID
OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID
OR @OFficeStaffID IS NULL)
AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID
OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID
OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
OR @SupervisorID IS NULL)
AND (tblPavingDetails.ReadyForInvoice IS NULL OR tblPavingDetails.ReadyForInvoice = 0)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
OR @CustomerSupervisorID IS NULL)
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
AND (tblPavingOnlyForm.WorkDate >= @StartDate OR @StartDate IS NULL)
AND (tblPavingOnlyForm.WorkDate <= @EndDate OR @EndDate IS NULL)
GROUP BY tblJobDetails.JobDescription,
tblPavingOnlyForm.ID,
tblPavingDetails.ID,
tblPavingOnlyForm.WorkDate,
tblPavingDetails.JobID,
tblUser.FullName,
tblUser.ID,
tblPavingDetails.ReviewDone, tblPavingDetails.ReadyForInvoice,
tblPavingDetails.CreatedBy,
statusPavingOnly.StatusName,
tblJobType.Title
UNION
SELECT tblFieldSupportServices.JobID, dbo.fnGetJobIDStringByJobId(tblFieldSupportServices.JobID) AS JobIDString, tblFieldSupportServices.ID AS FieldSupportID, tblFieldSupportServices.ReviewDone, NULL,
NULL, NULL, statusFieldSupport.StatusName,
tblJobDetails.JobDescription,
tblCustomer.FullName As Customer,
tblUser.FullName AS [Foreman],
tblUser.ID AS [ForemanID],
'Field Support' AS [FormType],
tblFieldSupportServices.Date,
NULL,
NULL,
((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbablePayItems
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblFieldSupportServices.Date
AND JobID = tblFieldSupportServices.JobID
AND (FieldSupportServicesID = tblFieldSupportServices.ID)
AND Total IS NOT NULL AND Total <> ''
AND tblProbablePayItems.IsDeleted IS NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS PayItem,
--((SELECT SUM((CAST(Total AS Decimal(20,2)))) FROM tblProbableChangeOrders
----INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
--WHERE WorkDate = tblForemanReports.WorkDate
--AND JobID = tblForemanReports.JobID
--AND (ForemanReportID = tblForemanReports.ID)
----AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
--AND InvoiceID IS NULL
--AND Total IS NOT NULL AND Total <> ''
--)) AS ChangeOrder,
NULLIF(((SELECT ISNULL(SUM((CAST(Total AS Decimal(20,2)))), 0) FROM tblProbableChangeOrders
--INNER JOIN tblStatus ON tblStatus.ID = tblProbableChangeOrders.StatusID
WHERE WorkDate = tblFieldSupportServices.Date
AND JobID = tblFieldSupportServices.JobID
AND (FieldSupportServicesID = tblFieldSupportServices.ID)
AND tblProbableChangeOrders.IsDeleted IS NULL
--AND (tblStatus.StatusName NOT LIKE 'Rejected by Customer Supervisor'
AND InvoiceID IS NULL
AND Total IS NOT NULL AND Total <> ''
)),0) AS ChangeOrder,
--(SUM(CAST(tblJobCostingSide.Amount AS Decimal(20,2)))) AS CostItem,
((SELECT SUM((CAST(Amount AS Decimal(20,2)))) FROM tblJobCostingSide
--INNER JOIN tblStatus ON tblStatus.ID = tblProbablePayItems.StatusID
WHERE WorkDate = tblFieldSupportServices.Date
AND JobID = tblFieldSupportServices.JobID
AND (tblJobCostingSide.FieldSupportServicesID = tblFieldSupportServices.ID)
AND tblJobCostingSide.Amount IS NOT NULL
--AND tblStatus.StatusName = 'Approved by Customer Supervisor'
)) AS CostItem,
0.0 AS InvoiceAmount,
CASE
WHEN (SELECT COUNT(tblChangeOrderGenerated.ID)
FROM tblChangeOrderGenerated Inner join tblChangeOrderGeneratedToDates
ON tblChangeOrderGenerated.ID = tblChangeOrderGeneratedToDates.ChangeOrderID
WHERE tblChangeOrderGenerated.JobID = tblFieldSupportServices.JobID AND tblChangeOrderGeneratedToDates.[Date] = tblFieldSupportServices.Date) > 0
THEN 1
ELSE 0
END AS IsChangeOrderGenerated,
tblJobType.Title AS JobType
FROM tblFieldSupportServices
LEFT JOIN tblUser ON LOWER(tblFieldSupportServices.CreatedBy) = LOWER(tblUser.Username)
LEFT JOIN tblJobCostingSide ON (tblJobCostingSide.FieldSupportServicesID = tblFieldSupportServices.ID
AND tblJobCostingSide.Amount IS NOT NULL)
INNER JOIN tblJobDetails ON tblJobDetails.ID = tblFieldSupportServices.JobID
INNER JOIN tblJobType ON tblJobDetails.JobTypeID = tblJobType.ID
INNER JOIN tblCustomer ON tblCustomer.ID = tblJobDetails.CustomerID
LEFT JOIN tblProbablePayItems ON tblProbablePayItems.FieldSupportServicesID = tblFieldSupportServices.ID
--LEFT JOIN tblSubContractorForm ON tblSubContractorForm.ID = tblProbablePayItems.SubcontractorFormID
LEFT JOIN tblStatus statusFieldSupport ON tblFieldSupportServices.StatusID = statusFieldSupport.ID
--LEFT JOIN tblJobToWorkOrder ON tblJobToWorkOrder.ID = tblFieldSupportServices.JobToWorkOrderID
LEFT JOIN tblScatteredServices ON
(tblScatteredServices.JobID = tblJobDetails.ID)
--AND tblScatteredServices.ID = tblJobToWorkOrder.ScatteredServiceID)
LEFT JOIN tblEncapsulationRequest ON
(tblEncapsulationRequest.JobID = tblJobDetails.ID)
--AND tblEncapsulationRequest.ID = tblJobToWorkOrder.EncapsulationRequestID)
LEFT JOIN tblMiscellaneousRequest ON
(tblMiscellaneousRequest.JobID = tblJobDetails.ID)
--AND tblMiscellaneousRequest.ID = tblJobToWorkOrder.MiscellaneousRequestID)
WHERE (tblJobDetails.CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (tblJobDetails.OfficeStaffID = @OFficeStaffID
OR tblMiscellaneousRequest.OfficeStaffID = @OFficeStaffID
OR @OFficeStaffID IS NULL)
AND ((@IsSupportServiceSupervisor = 1
AND ((tblFieldSupportServices.IsVacTruckAdded IS NULL
AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID
OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID
OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
OR @SupervisorID IS NULL))
OR (tblFieldSupportServices.IsVacTruckAdded = 1 OR @SupervisorID IS NULL)))
OR
(@IsSupportServiceSupervisor = 0 AND (tblJobDetails.NeuCoSupervisorID = @SupervisorID
OR tblScatteredServices.NeuCoSupervisorID = @SupervisorID
OR tblEncapsulationRequest.NeuCoSupervisorID = @SupervisorID
OR tblMiscellaneousRequest.NeucoSupervisorID = @SupervisorID
OR @SupervisorID IS NULL)))
AND (tblFieldSupportServices.ReviewDone IS NULL OR tblFieldSupportServices.ReviewDone = 0)
AND (tblProbablePayItems.InvoiceID IS NULL)
AND (tblJobDetails.ContractAreaID = @ContractAreaID OR @ContractAreaID IS NULL)
AND (tblJobDetails.ID = @JobID OR @JobID IS NULL)
AND (tblJobDetails.JobDescription like '%' + @JobDescription +'%' OR @JobDescription IS NULL)
AND (tblJobDetails.CustomerSupervisorID = @CustomerSupervisorID
OR tblScatteredServices.CustomerSupervisorID = @CustomerSupervisorID
OR tblMiscellaneousRequest.CustomerSupervisorID = @CustomerSupervisorID
OR tblEncapsulationRequest.CustomerSupervisorID = @CustomerSupervisorID
OR @CustomerSupervisorID IS NULL)
--AND (tblUser.ID = @ForemanID OR @ForemanID is Null)
AND (tblFieldSupportServices.[Date] >= @StartDate OR @StartDate IS NULL)
AND (tblFieldSupportServices.[Date] <= @EndDate OR @EndDate IS NULL)
GROUP BY tblJobDetails.JobDescription,
tblCustomer.FullName,
tblFieldSupportServices.ID,
tblFieldSupportServices.ForemanID,
tblFieldSupportServices.Date,
tblFieldSupportServices.JobID,
tblUser.FullName,
tblUser.ID,
tblJobCostingSide.ForemanReportID, tblJobCostingSide.WorkDate,
tblJobCostingSide.FieldSupportServicesID,
tblJobCostingSide.SubcontractorFormID,
tblJobCostingSide.JobID,
tblFieldSupportServices.ReviewDone,
tblFieldSupportServices.CreatedBy,
statusFieldSupport.StatusName,
tblJobType.Title
) AS ActiveForemanReports
WHERE
(ActiveForemanReports.JobID = @JobID OR (@JobID IS NULL AND @ForemanID IS NULL)
OR (ActiveForemanReports.JobID IN (SELECT * FROM @JobIDs) AND @JobID IS NULL ))) AS ActiveForemanReportsOuter
Where RowNumber >= @StartRowIndex and RowNumber <= @EndRowIndex OR (@StartRowIndex IS NULL AND @EndRowIndex IS NULL)
END
