Re: Select Counts & Date Ranges

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

select supplier

      ,count(distinct order_number)
     

,sum(decode(greatest(archive_date,trunc(sysdate)-5),archive_date,1,null)) from test_archive
[where ...]
group by supplier;

Note that the decode adds 1 to the sum iff archive_date >= trunc(sysdate)-5

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"