SQL close date

Navigation
0

Along with saying hello and thanks for this space, I ask for help with the following. I have two tables .. the table1. is a historical sales record with the fields fecha_vta, código_producto, cantidad. in the other table I have the variation of costs per product by date with the following fields date_costs, code_product, cost. The idea is that according to the sales history (table 1) look for the cost of the product (table 2) closest or equal to the sales date.

table 1 

fecha_vta                codigo_producto     cantidad 
--------                 ---------           ------------
5/5/2018                 prod1                      10
6/5/2018                 prod2                      5
6/5/2018                 prod1                      8
7/6/2018                 prod2                      20
15/6/2018                prod1                      3

Table 2 

fecha_costo            codigo_producto          costo
----------------       ---------------------    -------
01/05/2018             prod1                      2
06/05/2018             prod1                      2,5
06/05/2018             prod2                      3
09/06/2018             prod1                      2,8

expected result: 

fecha_vta                codigo_producto     cantidad                  costo                       fecha_costo
------------               ---------------------- -----------                  -------                      ----------------
5/5/2018                 prod1                      10                           2                              01/05/2018
6/5/2018                 prod2                      5                             3                              06/05/2018
6/5/2018                 prod1                      8                             2,5                           06/05/2018
7/6/2018                 prod2                      20                           3                              06/05/2018
15/6/2018                prod1                      3                             2,8                           09/06/2018

The idea is that the query brings me the cost of the product closest to the date_ cost to the sales_date. see in the result table that prod1 brings three different costs related to the proximity of the sale date with the date of cost of the product.

The closeness of the date is currently looking for the same or smaller results, but the next date should look for if it is the same, bigger or smaller and the closest one to bring it and show it.

**

on the cost date shows 01/05/2018 but must be 06/05/2018

**

I appreciate your help or advice to solve this problem.

    
asked by Sebastian Quijada 05.12.2018 в 17:01
source

1 answer

1

You can do it easily by creating a field that calculates the difference in days between one date and another, then sorting through that field and creating a ROW_NUMBER, so the first record of each invoice and product would take the cost.

Enclosed as the query would 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
    
answered by 05.12.2018 в 20:35
Mark a radio button according to the value of the database UnsatisfiedLinkError: no swt-pi-gtk-3550