Re: Select Counts & Date Ranges

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/12/10
Message-ID: <66lf33$ptv@bgtnsc03.worldnet.att.net>#1/1

Try this query (which uses inline views):

SELECT t1.supplier_code, "Order Backlog", "Weekly backlog" FROM test_archive t1, (SELECT COUNT(distinct order_number) "Order Backlog" FROM test_archive WHERE TRUNC(archive_date)<=TRUNC(SYSDATE)) t2, (SELECT COUNT(*) "Weekly Backlog" FROM test_archive WHERE (TRUNC(SYSDATE)-TRUNC(archive_date)<=5)) t3 WHERE t1.supplier_code=t2.supplier_code AND t1.supplier_code=t3.supplier_code
GROUP BY supplier_code;

Hope this helps

Michael Serbanescu

---

On Tue, 09 Dec 1997 16:51:22 -0600, Timothy.D.Lindsey_at_boeing.com wrote:

>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
Received on Wed Dec 10 1997 - 00:00:00 CST