SQL: List Days Past Due?

I'm running a test table called SUPPLIER. It has a column called "SUPP_DUE_DATE" for when the the supplier needs to get paid for example.

I'm trying to write a query that will find the days past due from that date.

I've started on this so far:

SELECT ROUND ( (MONTHS_BETWEEN (SUPP_DUE_DATE, 'SYSDATE') * 30), 0)
          num_of_days
  FROM DUAL
GROUP BY SUPP_DUE_DATE;

Open in new window


Would something like this work?? I think this is pretty clunky..and wont work. Open to suggestions!
LVL 4
Pancake_EffectAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, that looks good, except that I would do:

SELECT TRUNC(sysdate) - TRUNC(SUPP_DUE_DATE)
FROM SUPPLIER
WHERE SUPP_DUE_DATE < TRUNC(SYSDATE);

to avoid those that are due today. if you want to include those:
SELECT TRUNC(sysdate) - TRUNC(SUPP_DUE_DATE)
FROM SUPPLIER
WHERE SUPP_DUE_DATE < TRUNC(SYSDATE) + 1;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
'SYSDATE' is wrong, it would be SYSDATE (without the quotes)

next, instead of using months_between, you just put:
trunc(SYSDATE) - trunc(SUPP_DUE_DATE)

that will give you a number of days

I don't understand why you put GROUP BY , but let's check with above suggestion already if I "got you"?
0
 
Pancake_EffectAuthor Commented:
Is there a way to write this that it will only show the dates after the SUPP_DUE_DATE?

That we we don't get negative numbers if it's not the due date yet?

Using your example would it be something like this? Please correct me if I'm wrong.

SELECT TRUNC(sysdate) - TRUNC(SUPP_DUE_DATE) 
FROM SUPPLIER
WHERE SUPP_DUE_DATE < SYSDATE; 

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
awking00Commented:
select case when trunc(sysdate) <= trunc(supp_due_date) then 0
            else trunc(sysdate) - trunc(supp_due_date)
       end dayspastdue
0
 
awking00Commented:
I agree. It's just that I actually posted that response before I saw the Author's comment about only showing the dates after the Supp_Due_Date.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
my comment "that looks good.... " was directed to the asker, not to you, awking00
0
 
Pancake_EffectAuthor Commented:
Worked perfectly, and in terms I understand. Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.