SQL Query

Just a beginner with SQL and Stored procedures
Here is the scenario(A simplified version of it)

I have a table that has the following fields

Order_No
Due_Date

and say the table name is "Past_Due"

Now i need to write a stored procedure to return three fields in a cursor

The three fields are
1. No of orders that are 10 days past due(Due date + 10 < system date)

2. No of orders that are 20 days past due

3. No of orders that are 30 days past due.

Can any point out the most efficient way to write a sql for this in a stored procedure.

TIA
Narsi

do you mean u want the Order number of the orders or the count of the orders that are 10 days past the due date...

when u mean 10 days past the due date.. do u mean if an order is less than 10 days past the due date.. or just when it reaches the number 10..

If u stored the DueDate field as type Date u should be able to do this

DueDate-Date() which returns the how much the order os overdue by

I can help u better if u can answer the above 2 questions..

Here are couple of things I guess i failed to mention.

I need a single select query for doing this.

The Order_No field is not unique in the file and i need a count of the unique order, nos.

When i say 10 days past due i mean order that have been past due for the last 10 days and not 20 days. i. e. due date + 10 < sysdate and due date + 20 > sysdate

ok.. here we go..

the following query will return all the orders that are between 10 to 19 days beyond the due date..
when u minus two dates, the value returned is the number of days...

select Order_No,
sum(
iif(dueDate-Date()>9 AND dueDate-Date()<20, 1,0)
)
From yourTable
Group By Order_No;

the above query should return all orders that are 10 to 19 days overdue..

somethings to keep in mind.. iif() is not part of sql.. its part of VB, its an if else statement.. and should work in Access and MS SQL server
the way it works is.. iif(condition,ture,false)
e.g. iif(heightOver6Feet, tallPerson, normalPerson)

hopefully that helps..