-- I have the following tables:
table_ent | table_out
cod_prod date_ent vl_prod | cod_prod date_out vl_prod
362 14/09/2015 100,00 | 362 01/10/2016 700,00
362 15/09/2015 150,00 | 362 07/10/2016 800,00
362 16/09/2015 10,00 | 362 29/10/2016 100,00
362 05/10/2016 20,00 | 362 01/10/2016 800,00
362 06/10/2016 300,00 |
362 07/10/2016 460,00 |
362 08/10/2016 510,00 |
362 23/10/2016 620,00 |
362 24/10/2016 750,00 |
362 25/10/2016 810,00 |
362 30/10/2019 920,00 |
-- I need to get the closest date using SQL inner join. I already tried `max (date)` but it did not work out; I got this:
cod_ent date_out vl_ent cod_ent vl_ent date_ent
------ -------- ------ --- --- -------
362 01/10/16 700 362 100 14/09/15
362 01/10/16 800 362 10 16/09/15
362 01/10/16 700 362 150 15/09/15
362 01/10/16 700 362 10 16/09/15
362 01/10/16 800 362 150 15/09/15
362 01/10/16 800 362 100 14/09/15
362 07/10/16 100 362 20 05/10/16
362 07/10/16 100 362 300 06/10/16
362 07/10/16 100 362 100 14/09/15
362 07/10/16 100 362 10 16/09/15
362 07/10/16 100 362 150 15/09/15
362 29/10/16 920 362 510 08/10/16
362 29/10/16 920 362 750 24/10/16
362 29/10/16 920 362 460 07/10/16
362 29/10/16 920 362 10 16/09/15
--Desired Result :
cod_ent date_out vl_ent cod_ent vl_ent date_ent
---------- ------------ ------ ---------- -------- -----------
362 01/10/16 700 362 10 16/09/15
362 01/10/16 800 362 10 16/09/15
362 07/10/16 100 362 300 06/10/16
362 29/10/16 920 362 750 25/10/16
DROP TABLE TABLE_ENT;
CREATE TABLE TABLE_ENT (
COD_PROD VARCHAR(20),
DATE_ENT DATE,
VL_PROD NUMBER(19,2)
);
DROP TABLE TABLE_OUT;
CREATE TABLE TABLE_OUT (
COD_PROD VARCHAR(20),
DATE_OUT DATE,
VL_PROD NUMBER(19,2)
);
INSERT INTO TABLE_ENT VALUES ('362','14/09/2015','100,00');
INSERT INTO TABLE_ENT VALUES ('362','15/09/2015','150,00');
INSERT INTO TABLE_ENT VALUES ('362','16/09/2015','10,00');
INSERT INTO TABLE_ENT VALUES ('362','05/10/2016','20,00');
INSERT INTO TABLE_ENT VALUES ('362','06/10/2016','300,00');
INSERT INTO TABLE_ENT VALUES ('362','07/10/2016','460,00');
INSERT INTO TABLE_ENT VALUES ('362','08/10/2016','510,00');
INSERT INTO TABLE_ENT VALUES ('362','23/10/2016','620,00');
INSERT INTO TABLE_ENT VALUES ('362','24/10/2016','750,00');
INSERT INTO TABLE_ENT VALUES ('362','25/10/2016','810,00');
INSERT INTO TABLE_ENT VALUES ('362','30/10/2019','920,00');
INSERT INTO TABLE_OUT VALUES ('362','01/10/2016','700,00');
INSERT INTO TABLE_OUT VALUES ('362','01/10/2016','800,00');
INSERT INTO TABLE_OUT VALUES ('362','07/10/2016','100,00');
INSERT INTO TABLE_OUT VALUES ('362','29/10/2016','920,00');
COMMIT;
code used:
select a.cod_prod, a.date_out, a.vl_prod, b.cod_prod, sum(b.vl_prod) vl_prod, max(b.date_ent) as date_ent
from table_out a
join table_ent b on b.cod_prod = a.cod_prod and b.date_ent < a.date_out
group by a.cod_prod, a.date_out, b.cod_prod, a.vl_prod ,b.vl_prod
order by a.cod_prod, a.date_out;