H6贸易协议,到期前7天提醒

admin

SELECT
    a.BEGINDATE,
    a.ENDDATE,
    b.CODE,
    b.NAME,
    NVL(h.CTRBP2, '') AS CTRBP2
FROM (
    SELECT
        v.*,
        ROW_NUMBER() OVER (PARTITION BY v.NUM ORDER BY v.ENDDATE DESC) AS rn
    FROM HD40.VDRAGMTDTLINV v
    WHERE v.ENDDATE - SYSDATE < 8
    AND v.ENDDATE >= SYSDATE
) a
JOIN HD40.VENDOR b ON a.vdrgid = b.gid
LEFT JOIN (
    SELECT CODE, CTRBP2,
           ROW_NUMBER() OVER (PARTITION BY CODE ORDER BY CODE DESC) AS rhn
    FROM HD40.VENDORH
) h ON b.CODE = h.CODE AND h.rhn = 1
WHERE a.rn = 1