Help needed: sort of an aging query...

I have this query...

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:
00470871 12/27/2012 1/26/2013 Net 30 12 126.62 126.62
59355648 11/28/2012 12/28/2012 Net 30 41 11 538.75 538.75 538.75 538.75 538.75 538.75
75793062 12/4/2012 1/3/2013 Net 30 35 5 950 950 950 950 950
52835 12/13/2012 1/13/2013 Net 30 26 298.92 298.92 298.92
52814 12/4/2012 1/3/2013 Net 30 35 5 330 330 330 330 330


any sql gurus have ideas?

You need to use ranges based on your due_date and sysdate, something like:

Code:

case 
  when trunc( nps.due_date ) >= trunc( SYSDATE )
  then current
end as current

case
  when trunc( nps.due_date >= trunc( sysdate - 14 ) and trunc( nps.due_date ) < trunc( sysdate )
  then 0 to 14
end as 0 to 14

case
  when trunc( nps.due_date >= trunc( sysdate - 28 ) and trunc( nps.due_date ) < trunc( sysdate - 14 )
  then 15 to 28
end as 15 to 28