Aging Query - supressing values across subsequent columns
Oracle 11.2.0.3...
I have this query... working on it in SQL*Navigator.
We pay all vendors with Net 20, Net 25 and Net 30 terms in due date + 15 days. I'm trying to develop a query that will tell me who is due now, who will be due in the next 7, next 10, next 14 and next 21 days.
The query below returns not due, due, due +7, etc... but anything that is due is also showing up in the +7, +10, etc - because if it's due now, it will still be due in a week!
How can I suppress these values if they are already due?
(or, if they're due in +7 days, how can I suppress in +10, +14, etc?)
SELECT
pv.vendor_name "Vendor",
nps.invoice_number "Invoice",
nps.invoice_date "Invoice Date",
nps.due_date "Due Date",
inv.terms "Terms",
(TO_DATE (sysdate) - nps.invoice_date) "Days Entered",
(CASE
when to_date(nps.due_date) < to_date(sysdate)
then (to_date(sysdate) - to_date(nps.due_date))
else NULL
end) "Days Overdue",
nps.amount_remaining "Amount Remaining",
-- 0-15
(CASE
WHEN (TO_DATE (sysdate) - nps.invoice_date) < 45
THEN nps.amount_remaining
ELSE NULL
END
) "Not Due",
(CASE
WHEN (TO_DATE (sysdate) - nps.invoice_date) >= 45
THEN nps.amount_remaining
ELSE NULL
END
) "Due" ,
(CASE
WHEN (TO_DATE (sysdate+7) - nps.invoice_date) >= 45
THEN nps.amount_remaining
ELSE NULL
END
) "+7 Days" ,
(CASE
WHEN (TO_DATE (sysdate+10) - nps.invoice_date) >= 45
THEN nps.amount_remaining
ELSE NULL
END
) "+10 Days" ,
(CASE
WHEN (TO_DATE (sysdate+14) - nps.invoice_date) >= 45
THEN nps.amount_remaining
ELSE NULL
END
) "+14 Days" ,
(CASE
WHEN (TO_DATE (sysdate+21) - nps.invoice_date) >= 45
THEN nps.amount_remaining
ELSE NULL
END
) "+21 Days"
FROM
inv,
pv,
nps
WHERE
and nps.amount_remaining <> 0
and inv.terms in ('Net 20', 'Net 25', 'Net 30')
sample output:
Vendor Invoice Invoice Date Due Date Terms Days Entered Days Overdue Amount Remaining Not Due Due +7 Days +10 Days +14 Days +21 Days
vendor 1 00470871 12/27/2012 1/26/2013 Net 30 12 126.62 126.62
vendor 2 59355648 11/28/2012 12/28/2012 Net 30 41 11 538.75 538.75 538.75 538.75 538.75 538.75
vendor 3 75793062 12/4/2012 1/3/2013 Net 30 35 5 950 950 950 950 950
vendor 4 52835 12/13/2012 1/13/2013 Net 30 26 298.92 298.92 298.92
vendor 4 52814 12/4/2012 1/3/2013 Net 30 35 5 330 330 330 330 330
any sql gurus have ideas?
Edited by: camforbes on Jan 8, 2013 1:24 PM