Select Counts & Date Ranges

I want to do counts on an archive file based on selected dates. One of the counts will be a date range and the rest will be counts for the current date (SYSDATE).

If my test_archive table contains:

Supplier Code	        Order Number	Archive Date
AA			111111		01-DEC-97
AA			111111		02-DEC-97
AA			111111		03-DEC-97
AA			111111		04-DEC-97
AA			111111		05-DEC-97
AA			222222		03-DEC-97
AA			222222		04-DEC-97
AA			222222		05-DEC-97

I want to end up with one count that represents the order backlog for the current day (In this case the SYSDATE would be 05-DEC-97) and another with a count of all the orders over the last 5 calendar days. Order number is unique for each date.

My output fields should be supplier code, current backlog and weekly backlog count:

AA 002 008

Can I do this in a single select?

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet