How to make a SELECT to get the value of the last registered date

Question

Hello, I would like a help to always use the value of a table. I have a table "COSTS" and every month it suffers price change of the products, but are not all that has update

My question is that whenever I need to use the COST table with the PRODUCT table, as I always call the last updated date of the product. I will show the code I created, but I can not show the value of the last cost table.

I thank you already this is a short excerpt of the study more meets the basics I need to explain and understand.

CTE.DTCONTAGEM
,CTE.CODEMP
,PRO.NCM
,PRO.DESCRPROD
,CTE.CODPROD
,PRO.AD_PARTNUMBER
,CTE.CODVOL
,CTE.QTDEST

---na tabela de custo tem esse campo CUSSEMICM que preciso que seja sempre a ultima data inserida
,CUS.CUSSEMICM

-- esse código era só um teste porém traz qualquer valor do custo e não o ultimo
,(SELECT nvl(MAX(FCUS.CUSSEMICM), 0) 
  FROM TGFCUS FCUS
  WHERE FCUS.CODPROD = CTE.CODPROD
  AND FCUS.DTATUAL < trunc (SYSDATE) -1) AS CUSTO

,CUS.CUSSEMICM
,CTE.QTDEST * CUS.CUSSEMICM AS CUS_SEM_ICMS -- esse é o calculo que precisa fazer mais o custo tem que ser o certo
,CUS.CUSMEDCALC
,CTE.CODLOCAL
,CTE.CODPARC
,CTE.TIPO
FROM
VGFCTE CTE
LEFT JOIN TGFCUS CUS ON (CUS.CODPROD = CTE.CODPROD AND CUS.CODEMP = CTE.CODEMP AND CTE.CODLOCAL = CUS.CODLOCAL)
LEFT JOIN TGFPRO PRO ON (CTE.CODPROD = PRO.CODPROD )
WHERE CTE.TIPO ='P' AND (PRO.CODGRUPOPROD >= 30101000 AND PRO.CODGRUPOPROD <= 31601000)
AND CTE.CODLOCAL IN (10000000,20000000)
AND CTE.QTDEST > 0

Answer 1

try something like

,CUS.CUSSEMICM

-- esse código era só um teste porém traz qualquer valor do custo e não o ultimo


,CUS.CUSSEMICM
,CTE.QTDEST * CUS.CUSSEMICM AS CUS_SEM_ICMS -- esse é o calculo que precisa fazer mais o custo tem que ser o certo
,CUS.CUSMEDCALC
,CTE.CODLOCAL
,CTE.CODPARC
,CTE.TIPO
FROM
VGFCTE CTE
LEFT JOIN TGFCUS CUS ON (CUS.CODPROD = CTE.CODPROD AND CUS.CODEMP = CTE.CODEMP AND CTE.CODLOCAL = CUS.CODLOCAL)
LEFT JOIN TGFPRO PRO ON (CTE.CODPROD = PRO.CODPROD )
WHERE CTE.TIPO ='P' AND (PRO.CODGRUPOPROD >= 30101000 AND PRO.CODGRUPOPROD <= 31601000)
AND CTE.CODLOCAL IN (10000000,20000000)
AND CTE.QTDEST > 0

AND CUS.CUSSEMICM = (SELECT nvl(MAX(FCUS.CUSSEMICM), 0) 
                      FROM TGFCUS FCUS
                      WHERE FCUS.CODPROD = CUS.CODPROD
                      AND   FCUS.CODEMP = CUS.CODEMP 
                      AND   FCUS.CODLOCAL = CUS.CODLOCAL) 

Answer 2

Man, thank you so much. That balcony hadn’t touched me, it opened my mind. I redid the code and left it that way based on what you gave me. I just have to thank.

       NCM,
       AD_PARTNUMBER,
       CTE.DTCONTAGEM, CTE.CODPROD,
       (SELECT DESCRPROD FROM TGFPRO WHERE CODPROD = CTE.CODPROD) AS PRODUTO, CTE.CODVOL,
       CTE.CODLOCAL || '-' ||
       (SELECT L.DESCRLOCAL FROM TGFLOC L WHERE L.CODLOCAL = CTE.CODLOCAL) AS LOCAL,
       CTE.QTDEST,  CASE WHEN CTE.TIPO = 'P' THEN 'PRÓPRIO' ELSE 'TERCEIROS' END AS TIPO,


NVL((SELECT NVL(MAX(CUSSEMICM),0)
     FROM TGFCUS
     WHERE CODPROD = CTE.CODPROD
     AND CODEMP = CTE.CODEMP
     AND DTATUAL = (SELECT MAX(DTATUAL)
                       FROM TGFCUS CN
                       WHERE CODPROD = CTE.CODPROD
                         AND DTATUAL <= CTE.DTCONTAGEM
                         AND CODEMP = CTE.CODEMP
                     )
      ) * CTE.QTDEST,0 ) AS CUS_SEMICMS,
CTE.CODEMP

,(SELECT NVL(MAX(CUSSEMICM),0)
     FROM TGFCUS
     WHERE CODPROD = CTE.CODPROD
     AND CODEMP = CTE.CODEMP
     AND DTATUAL = (SELECT MAX(DTATUAL)
                       FROM TGFCUS CN
                       WHERE CODPROD = CTE.CODPROD
                         AND DTATUAL <= CTE.DTCONTAGEM
                         AND CODEMP = CTE.CODEMP
                     )
      ) AS CUS_ITEM_SEMICMS

FROM TGFCTE CTE
INNER JOIN TGFPRO PRO ON (CTE.CODPROD = PRO.CODPROD AND (PRO.CODGRUPOPROD >= 30101000 AND PRO.CODGRUPOPROD <= 31601000))
WHERE CTE.DTCONTAGEM = TO_DATE('24/12/2020','DD/MM/YYYY')
AND CTE.CODEMP = 6
AND CTE.QTDEST > 0