Re: Select Counts & Date Ranges

From: Timothy D Lindsey <Timothy.D.Lindsey_at_Boeing.com>
Date: 1997/12/11
Message-ID: <349001D8.23A3@Boeing.com>#1/1

Michael Serbanescu wrote:
>
> 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?
> >

        I noticed a potential problem with my solution. If any of the sub-selects result in no records then there is no summary record output. The solution to this is to do an outer join to the sub-select tables. This forces a summary record to be output and any sub-selects with no data result in a null value.

SELECT t1.suppliercode,

       "Cur Backlog",
       "Weekly Backlog"
FROM   test_archive t1,
       (SELECT suppliercode, COUNT(ordernum) "Cur Backlog"
          FROM test_archive 
         WHERE TRUNC(archive_date) = TRUNC(SYSDATE)
         GROUP BY suppliercode) t2,
       (SELECT suppliercode, COUNT(ordernum) "Weekly Backlog"
          FROM test_archive 
         WHERE TRUNC(SYSDATE)-TRUNC(archive_date)<=5 
         GROUP BY suppliercode) t3

WHERE t1.suppliercode=t2.suppliercode(+)   AND t1.suppliercode=t3.suppliercode(+) GROUP BY t1.suppliercode,"Cur Backlog","Weekly Backlog";

-- 


Boeing Fabrication Division Information Systems 
e-mail Timothy.D.Lindsey_at_boeing.com

Received on Thu Dec 11 1997 - 00:00:00 CST