Onderstaande query toont een lijst met crediteuren inclusief de laatste mutatiedatum in Globe. Crediteuren zonder mutaties krijgen mutatiedatum 1-1-1900 mee.
SELECT Max(gbkmut.datum) AS lastdate,
c2.crdcode AS CrdNr,
c2.cmp_name AS NAME,
c2.cmp_type AS Type,
c2.cmp_status AS status
FROM gbkmut
JOIN cicmpy c2
ON gbkmut.crdnr = c2.crdnr
AND c2.crdnr IS NOT NULL
AND gbkmut.debnr IS NULL
WHERE gbkmut.transtype IN ( 'N', 'C', 'P' )
AND bud_vers IS NULL
AND gbkmut.remindercount <= 10
AND gbkmut.bkstnr IS NOT NULL
AND gbkmut.oorsprong <> 'S'
AND Isnull(gbkmut.transsubtype, '') <> 'X'
GROUP BY gbkmut.crdnr,
c2.crdcode,
c2.cmp_name,
c2.cmp_type,
c2.cmp_status
UNION ALL
SELECT '1900-01-01 00:00:00.000',
crdcode,
cmp_name,
cmp_type,
cmp_status
FROM cicmpy
WHERE crdnr IS NOT NULL
AND debnr IS NULL
AND crdnr NOT IN (SELECT crdnr
FROM gbkmut
WHERE gbkmut.transtype IN ( 'N', 'C', 'P' )
AND bud_vers IS NULL
AND gbkmut.remindercount <= 10
AND gbkmut.bkstnr IS NOT NULL
AND gbkmut.oorsprong <> 'S'
AND Isnull(gbkmut.transsubtype, '') <> 'X'
AND gbkmut.crdnr IS NOT NULL)