Using SQL Server 2008.
The problem is it's bringing back duplicate OrderID.
I've marked the problem joins.
Even tried OUTER APPLY but it caused certain searches not to work.
Tried INNER APPLY then duplicates again.
The problem joins, the tables, have multiple references to OrderID.
So tblRun has multiple rows with the same OrderID showing which run it was in at and what date and so forth.
I really need suggestions from all the guru's out there.
Here is the sql:
DECLARE @CompanyID INT = 22718,
@StartDate DATETIME = '',
@EndDate DATETIME = '',
@SalesRepID INT = NULL,
@AssignedTo INT = NULL,
@ServiceDefID INT = NULL,
@ProductName VARCHAR(512) = NULL,
@IsCCOrder BIT = NULL,
@OrderID INT = NULL,
@LocationID INT = NULL,
@SalesRepLocationID INT = NULL,
@PONum VARCHAR(150) = NULL,
@InvoiceID INT = NULL,
@IsSplitOrder BIT = NULL,
@ContactID INT = NULL,
@ContactName VARCHAR(150) = NULL,
@JobName VARCHAR(200) = NULL,
@Notes VARCHAR(MAX) = NULL,
@CompanyName VARCHAR(255) = NULL,
@DueDateFrom DATETIME = '',
@DueDateTo DATETIME = '',
@SubmitedDateFrom DATETIME = '',
@SubmitedDateTo DATETIME = '',
@IsRush BIT = NULL,
@Msg VARCHAR(1000) = NULL
--@Stages dbo.int_tbltype READONLY
DECLARE @Stages TABLE (ID INT)
--INSERT INTO @Stages (ID) VALUES (1)
DECLARE @DueDate DATETIME = NULL
SET NOCOUNT ON
DECLARE @OrderIDsTBL TABLE(OrderID INT)
IF @Msg IS NOT NULL
BEGIN
INSERT INTO @OrderIDsTBL (OrderID)
SELECT OrderID FROM tblOrderLog
WHERE Msg LIKE '%' + @Msg + '%'
END
IF @OrderID IS NOT NULL
BEGIN
INSERT INTO @OrderIDsTBL (OrderID)
VALUES (@OrderID)
END
DECLARE @OderIDsCnt INT = (SELECT COUNT(OrderID) FROM @OrderIDsTBL)
DECLARE @StageCnt INT = (SELECT COUNT(ID) FROM @Stages)
SELECT
o.OrderID,
o.CompanyID,
co.Name AS CompanyName,
o.ContactID,
o.JobName,
p.FirstName + ' ' + p.LastName AS ContactName,
p2.FirstName + ' ' + p2.LastName AS SalesRep,
o.DueDate,
CASE WHEN MAX(oi.PriorityService) > 0 THEN 1 ELSE 0 END AS IsRush,
ISNULL(s.StageID, 0) AS StageID,
o.Notes, r.SubmittedComplete,
dbo.fOrderRunLocationCSVByOrderID(o.OrderID) AS LocationCSV,
(SELECT
STUFF((SELECT DISTINCT ' ' + st.Name + '<br />' FROM tblStage st
INNER JOIN tblOrderItem oi ON oi.OrderID = o.OrderID
INNER JOIN tblRun r ON r.OrderItemID = oi.OrderItemID
INNER JOIN tblStage s ON s.StageID = r.StageID
LEFT JOIN tblRunService rs ON rs.RunID = r.RunID
WHERE (s.StageID = st.StageID)
AND (rs.AssignedTo = @AssignedTo OR @AssignedTo IS NULL)
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, ''))
AS Stages,
Row_Number() Over(Order By o.OrderID Desc) As RowNum
FROM tblOrder o
INNER JOIN tblCompany co ON co.CompanyID = o.CompanyID
INNER JOIN tblParty p ON p.PartyID = o.ContactID
-------- PROBLEM JOINS ------------
**LEFT JOIN tblOrderItem oi ON oi.OrderID = o.OrderID**
**LEFT JOIN tblRun r ON r.OrderItemID = oi.OrderItemID**
**LEFT JOIN tblService srv ON srv.OrderItemID = oi.OrderItemID**
-------- END PROBLEM JOINS ------------
--OUTER APPLY
-- (
-- SELECT TOP (1) OrderItemID, Name, PriorityService, Notes,
-- SplitOrderID
-- FROM tblOrderItem oi
-- WHERE oi.OrderID = o.OrderID
-- ) oi
-- OUTER APPLY
-- (
-- SELECT TOP (1) SubmittedComplete, StageID
-- FROM tblRun r
-- WHERE r.OrderItemID = oi.OrderItemID
-- ORDER BY SubmittedComplete DESC
--) r
--OUTER APPLY
--(
-- SELECT TOP (1) ServiceID, ServiceDefID, LocationID
-- FROM tblService srv
-- WHERE srv.OrderItemID = oi.OrderItemID
-- ORDER BY ServiceID DESC
--) srv
LEFT JOIN tblStage s ON s.StageID = r.StageID
LEFT JOIN tblParty p2 ON p2.PartyID = o.SalesRepID
LEFT JOIN tblEmployee e ON e.EmployeeID = o.SalesRepID
LEFT JOIN tblShipTo st ON o.ShipToID = st.ShipToID
WHERE
(@CompanyID IS NULL OR ( o.CompanyID = @CompanyID )) AND
(@IsCCOrder IS NULL OR ( ISNULL(o.IsCreditCardOrder, 0) = @IsCCOrder )) AND
(@SalesRepID IS NULL OR o.SalesRepID = @SalesRepID ) AND
(@ServiceDefID IS NULL OR ( srv.ServiceDefID = @ServiceDefID )) AND
(@ProductName IS NULL OR ( oi.Name LIKE '%' + @ProductName + '%')) AND
(@IsSplitOrder IS NULL OR ( @IsSplitOrder = 1 AND oi.SplitOrderID IS NOT NULL)) AND
(
( @StartDate = '' OR @EndDate = '' ) OR
( @StartDate >= CreatedDate AND @EndDate <= COALESCE(CancelledDate, ClosedDate, GetDate()) ) OR
( @StartDate <= COALESCE(CancelledDate, ClosedDate, GETDATE()) AND @EndDate >= COALESCE(CancelledDate, ClosedDate, GetDate()) ) OR
( @StartDate <= CreatedDate AND @EndDate >= CreatedDate )
) AND
(@LocationID IS NULL OR (@LocationID = srv.LocationID OR srv.LocationID IS NULL)) AND
(@SalesRepLocationID IS NULL OR (@SalesRepLocationID = e.LocationID OR e.LocationID IS NULL))
AND (@InvoiceID IS NULL OR o.InvoiceID = @InvoiceID )
AND (@PONum IS NULL OR o.PONum LIKE '%' + @PONum + '%')
AND (COALESCE(s.StageID, 0) IN (SELECT ID FROM @Stages) OR @StageCnt = 0)
AND (o.ContactID = @ContactID OR @ContactID IS NULL)
AND (p.FirstName + ' ' + p.LastName LIKE '%' + @ContactName + '%' OR @ContactName IS NULL)
AND (o.JobName LIKE '%' + @JobName + '%' OR @JobName IS NULL)
AND (o.Notes LIKE '%' + @Notes + '%' OR @Notes IS NULL)
AND (co.Name LIKE '%' + @CompanyName + '%' OR @CompanyName IS NULL)
AND (o.DueDate >= @DueDateFrom OR @DueDateFrom = '')
AND (o.DueDate <= @DueDateTo OR @DueDateTo = '')
AND (r.SubmittedComplete >= @SubmitedDateFrom OR @SubmitedDateFrom = '')
AND (r.SubmittedComplete <= @SubmitedDateTo OR @SubmitedDateTo = '')
AND (@IsRush = (CASE WHEN oi.PriorityService > 0 THEN 1 ELSE 0 END)
OR @IsRush IS NULL)
AND (o.OrderID IN (SELECT OrderID FROM @OrderIDsTBL) OR @OderIDsCnt = 0)
GROUP BY
o.OrderID,
o.CompanyID,
co.Name,
o.ContactID,
o.JobName,
p.FirstName,
p.LastName,
p2.FirstName,
p2.LastName,
o.DueDate,
o.Notes,
r.SubmittedComplete,
s.StageID
Thanks for any suggestions. I've been working on this for some time now and just can't get it working right.