SQL Query

here is my requirement

I have these fields in a table

Due_Date
Cheque_Date
Fiscal_Year_Period

Due_Date is the date when a payment is due
Cheque_Date is the date when the payment was actually made.
and Fiscal_Year_Period is the fiscal year for that record.

When a due_date is past the payment is considered past_due. If the payment is made say 31 days after due_date the payment had been past_due for 30 days.


I need a SQL that returns past due payments for each fiscal_year_period and broken down into four different buckets for each of those fiscal_year_period.

The first bucket is 0 - 30 days past due(Which means the payment had been past due for 30 days)(cheque_date - due_date)
The second bucket is 30 - 60 days past due(Which mean the payment had been past due for more than 30 days but less than 60 days)
The third bucket is 60 - 90 days
and finally greater than 365 days.

Now a payment could be past due when a cheque has not been made at all till today which mean the cheque_date would be blank and in this case the bucket it falls into would be determined by the comparison of today(Sysdate) to the due_date

TIA
Narsi

what database?

Oracle Database and what i forgot to mention is that i need this in a stored procedure.

Hi narsibvl,

The Devshed community enjoys helping one another, but there are a couple recommendations to get the best results:

1. Descriptive titles to your questions -- "SQL Query" is not very descriptive. 99% of the questions in the Database section involve SQL queries . Your question title could be something like: "Date calculations in Oracle stored procedure".

2. At least attempt to solve your problem in some way, before posting a question. The "geek" community is much more interested in helping people that way. If you have tried some queries to solve your problem already, please post those, and explain what error messages you get, or why the query doesn't give the results you want.

Best regards,

rycamor

Keeping in mind the moderators views i hereby change the subject to the above mentioned.

Also here is a query i have which returns the records and assigns different buckets to it but i need a count of the buckets not just the individual records.


select nvl(cheque_date,sysdate),due_date, fiscal_year_period,
(
case
when (nvl(cheque_date,sysdate) - due_date) <= 30 then 'Bucket_1'
when (nvl(cheque_date,sysdate) - due_date) > 30 and (cheque_date - due_date) <= 60 then 'Bucket_2'
when (nvl(cheque_date,sysdate) - due_date) > 60 and (cheque_date - due_date) <= 90 then 'Bucket_3'
else 'Bucket_4'
end
) Bucket
from GETS_GS_AR_AGING_DETAIL