Re: Complicated Query

On Sep 12, 6:53 pm, artme..._at_yahoo.com wrote:
> Hi,
>
> I'm trying to write a complicted query, maybe someone can help. Here
> is the table:
>
> ACTION
> DATE
> PRODUCT
>
> The ACTION column contains 2 values (A, D).  What I want to do is to
> get a count, by PRODUCT for each ACTION type for each MONTH range.
>
> So, a count for product 1 with an action of A for 8/1/08 - 8/31/08.
> Then, a count for product 1 with an action of D for 8/1/08 - 8/31/08.
>
> So, a count for product 2 with an action of A for 8/1/08 - 8/31/08.
> Then, a count for product 2 with an action of D for 8/1/08 - 8/31/08.
>
> Etc.....
>
> I think this can be done with an analytical function of some sorts,
> rather than writing some PL/SQL code.  I'm searching, but maybe
> someone else can help......
>
> Thank you much!

Ok, I was playing with another table. Please look at this:

   SELECT status, TRUNC(order_date) order_date, sum(sum(total_amount))    OVER(PARTITION BY status

        ORDER BY TRUNC(order_date) RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) mavg

   FROM customer_order
   WHERE order_date > TO_DATE('09012008','MMDDYYYY')    GROUP BY status, TRUNC(order_date)
ORDER BY TRUNC(order_date);

Now, that does not work and do what I want. What I want is it to give me 8/31/08 and 9/02/08. I mean, my window started at 9/01/08. I thought that it would take that and give me the 1 day before and 1 day after......

Eventually I want to be able to get some counts within a month range, as I said in my previous post.......these analytical functions are a bit weird. But it seems like the window is not working. Heck, I can just use order_date and subtract 1 day and add 1 day.......so what is the difference????

I want to give it a start date and have it sum or count the numbers by MONTH......... Received on Fri Sep 12 2008 - 20:45:25 CDT