S
You can make it easy by creating a field that calculates the difference in days between one date and another, then ordering for that field and creating a ROW_NUMBER, so the first record of each invoice and product would take the cost. Attached as the consultation will be according to the structure and data you give. DECLARE @tabla1 TABLE (fecha_vta DATE, codigo_producto VARCHAR(10), cantidad INT)
DECLARE @tabla2 TABLE (fecha_costo DATE, codigo_producto VARCHAR(10), costo DECIMAL(20,2))
INSERT INTO @tabla1 VALUES('2018-05-05', 'prod1', 10)
INSERT INTO @tabla1 VALUES('2018-05-06', 'prod2', 5)
INSERT INTO @tabla1 VALUES('2018-05-06', 'prod1', 8)
INSERT INTO @tabla1 VALUES('2018-06-07', 'prod2', 20)
INSERT INTO @tabla1 VALUES('2018-06-15', 'prod1', 3)
INSERT INTO @tabla2 VALUES('2018-05-01', 'prod1', 2)
INSERT INTO @tabla2 VALUES('2018-05-06', 'prod1', 2.5)
INSERT INTO @tabla2 VALUES('2018-05-06', 'prod2', 3)
INSERT INTO @tabla2 VALUES('2018-06-09', 'prod1', 2.8)
;WITH CTE_Cercania AS
(
SELECT T1.*,
T2.costo,
T2.fecha_costo,
ABS(DATEDIFF(DAY,T1.fecha_vta,T2.fecha_costo)) Cercania,
ROW_NUMBER() OVER(PARTITION BY T1.fecha_vta, T1.codigo_producto ORDER BY ABS(DATEDIFF(DAY,T1.fecha_vta,T2.fecha_costo)) ASC) OrdenCercania
FROM @tabla1 T1
LEFT JOIN @tabla2 T2 ON T1.codigo_producto = T2.codigo_producto
--ORDER BY T1.codigo_producto, ABS(DATEDIFF(DAY,T1.fecha_vta,T2.fecha_costo)) ASC
)
SELECT * FROM CTE_Cercania WHERE OrdenCercania = 1
