根据本星期逾期未交的系统，该日期是如何逾期的？

Question

我需要满足以下要求

•   Overdue 
o   This Week (Count)
o   Past 3 Weeks (Count)
o   Beyond 3 Weeks (Count)
•   Due
o   This Week (Count)
o   Within 3 Weeks (Count)
o   Beyond 3 Weeks (Count)

我已经尝试了以下查询

select to_date('05-MAY-17') > sysdate-6 and
to_date('05-MAY-17') < sysdate-6 then 'Y'
else 'N' from dual;

这是我硬编码的一周..。我不确定这是不是对的？请给我建议更好的方法来满足我的要求。

提前谢谢。

Answer 1

想必您应该在类似库应用程序的东西中查询表。所以，您需要的是这样一堆条件计数：

select 
   count( case when due_date < sysdate
                and due_date > sysdate-7 then 1 end ) overdue_one_week
   , count( case when due_date <= sysdate-7 
                  and due_date > sysdate-21 then 1 end ) overdue_three_week
   , count( case when due_date <= sysdate-21 then 1 end ) overdue_longer
   , count( case when due_date >= sysdate 
                  and due_date < sysdate+7 then 1 end ) due_this_week
   , count( case when due_date >= sysdate+7 
                  and due_date < sysdate+21 then 1 end ) due_three_weeks
   , count( case when due_date >= sysdate+21 then 1 end ) due_beyond_three_weeks
from library_loans
/