 | |
#1
 
| |||
| |||
|
Complicated Query -
09-12-2008
, 06:53 PM
#2
| |||
| |||
|
|
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.........
#3
| |||
| |||
|
|
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.........
#4
| |||
| |||
|
|
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.........
#5
| |||
| |||
|
|
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.........
#6
| |||
| |||
|
|
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! |
No need to analytics there, good old aggregate.
Regards
Michel
#7
| |||
| |||
|
|
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! |
No need to analytics there, good old aggregate.
Regards
Michel
#8
| |||
| |||
|
|
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! |
No need to analytics there, good old aggregate.
Regards
Michel
#9
| |||
| |||
|
|
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! |
No need to analytics there, good old aggregate.
Regards
Michel
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.