Re: Complicated Query
Date: Fri, 12 Sep 2008 18:45:25 -0700 (PDT)
On Sep 12, 6:53 pm, artme..._at_yahoo.com wrote:
> I'm trying to write a complicted query, maybe someone can help. Here
> is the table:
> 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.
> 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
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