Ps bueno despues de tantos tumbo consegui que la consulta me arrojara datos de la siguiente manera:
Código SQL:
Ver originalSELECT D.Art_id, D.Detalle_Cantidad, D.Detalle_Costo_Unitario , CASE isnull(O.Fecha_Desde,0) WHEN 0 THEN 0 ELSE 3 END Valor
FROM Com_Ofertas O WITH(Nolock)
LEFT JOIN Orden_Loc_Detalle D WITH(NoLock)
ON O.Emp_id = D.Emp_id AND O.Suc_id = D.Suc_id AND O.Art_id = D.Art_id
AND D.Detalle_Envio_Prov = 'si' AND O.Prov_id = 316
AND CONVERT(VARCHAR(10),Getdate(),112) BETWEEN
CONVERT(VARCHAR(10),O.Fecha_Desde,112) AND
CONVERT(VARCHAR(10),O.Fecha_Hasta,112)
WHERE D.Emp_id = 1
AND D.Orden_id = 29279
AND D.Suc_id = 2
ORDER BY Valor
Lo que me faltaba era para mi tabla Com_Ofertas agregar todas las condiciones que tubieran que ver con el y lo que tambien cambie fue el RIGHT por el LEFT para no cambiar de posición mis tablas y eso fue todo espero que alguien le pueda ayudar este caso.
Gracias y saludos