#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    1
    Rep Power
    0

    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?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    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

IMN logo majestic logo threadwatch logo seochat tools logo