Help with sql server query.

See more:

SQL

SQL-Server

Hey guys, I have a query which to me looks messey, I'm just wondering if someone can simplify it for me as there has to be a neater way to return the data.

SELECT DestinationName AS [Destination], cp.RoutingRank, cp.LCRRank AS [LCR Rank], cp.LCRRate AS [Carrer Rate], cp1.LCRRate AS [LCR Rate], cp2.LCRRate AS [Routing Rate],
c1.CarrierShortName AS [LCR Carrier], c2.CarrierShortName AS [1st Carrier], cp1.CarrierID AS [LCRCarrierId], 
ISNULL(ovr.CarrierId, ex.CarrierId) AS [Override Carrier], ISNULL(ovr.ExclusionTypeId, ex.ExclusionTypeId) AS [Exclusion Type]
FROM dbo.Destination d WITH(NOLOCK)
LEFT OUTER JOIN dbo.CarrierPosition cp WITH(NOLOCK) ON d.DestinationId = cp.DestinationId


INNER JOIN 

--Sub Query 1
(SELECT RoutingPlanScheduleId, AnalysisDate, Max(RoutingPlanId) AS RoutingPlanId 
FROM dbo.RoutingPlan WITH(NOLOCK) GROUP BY RoutingPlanScheduleId, AnalysisDate) rp
ON rp.RoutingPlanId = cp.RoutingPlanId


INNER JOIN dbo.RoutingPlanSchedule rps WITH(NOLOCK) ON rps.RoutingPlanScheduleId = rp.RoutingPlanScheduleId
INNER JOIN dbo.CarrierPosition cp1 WITH(NOLOCK) ON cp1.DestinationId = cp.DestinationId  AND cp1.RoutingPlanId = cp.RoutingPlanId AND cp1.LCRRank = 1
INNER JOIN dbo.Carrier c1 WITH(NOLOCK) ON c1.CarrierId = cp1.CarrierId
INNER JOIN dbo.CarrierPosition cp2 WITH(NOLOCK) ON cp2.DestinationId = cp.DestinationId  AND cp2.RoutingPlanId = cp.RoutingPlanId AND cp2.RoutingRank = 1
INNER JOIN dbo.Carrier c2 WITH(NOLOCK) ON c2.CarrierId = cp2.CarrierId
LEFT OUTER JOIN 


-- Sub Query 2
(SELECT ISNULL(e.CarrierId,p.CarrierId) AS CarrierId, ExclusionTypeId, DestinationBandId
FROM dbo.ExclusionDestinationBand edb WITH(NOLOCK)
INNER JOIN dbo.Exclusion e WITH(NOLOCK) ON e.ExclusionId = edb.ExclusionId
INNER JOIN dbo.ExclusionDetail ed WITH(NOLOCK) On ed.ExclusionId = e.ExclusionId 
LEFT OUTER JOIN dbo.ROProportion p WITH(NOLOCK) ON p.ExclusionDetailId = ed.ExclusionDetailId
WHERE ed.Deleted=0 AND GetDate() BETWEEN ed.ActiveFrom AND ed.ActiveTo
AND e.ExclusionTypeId IN (3,4,5,6)) ovr
ON ovr.DestinationBandId = d.DestinationBandId

-- Sub Query 3
LEFT OUTER JOIN 
(SELECT CarrierId, ExclusionTypeId, DestinationBandId
FROM dbo.Exclusion e2 WITH(NOLOCK)
INNER JOIN dbo.ExclusionDetail ed2 WITH(NOLOCK) On ed2.ExclusionId = e2.ExclusionId 
LEFT OUTER JOIN dbo.ExclusionDestinationBand edb2 WITH(NOLOCK) ON edb2.ExclusionId = e2.ExclusionId
WHERE ed2.Deleted=0 AND GetDate() BETWEEN ed2.ActiveFrom AND ed2.ActiveTo
AND e2.ExclusionTypeId NOT IN (3,4,5,6)) ex 
ON ex.CarrierId  = cp1.CarrierId AND (ex.ExclusionTypeId = 1 OR ex.DestinationBandId = d.DestinationBandId)

--Where clause for initial query
WHERE cp.CarrierId = @CarrierID
AND rps.ROsettingId = @ROSetting
--AND rps.CommercialSwitch = 0
AND rp.AnalysisDate = @AnalysisDate
ORDER BY DestinationName

Thanks in advance guys