 | |
#11
 
| |||
| |||
|
Re: Query -
09-27-2008
, 03:27 PM
|
m... (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5c... (AT) m44g2000hsc (DOT) googlegroups.com... | | Hi, | | I am beginning to like these analytical functions. Especially with | 10g they seem to have a lot of options. Anyhow, I have a simple query | like this: | | SELECT s.customer_id, c.signup_date, s.produst_id, s.status FROM | subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND status > 1 | AND produst_id = 1 | AND start_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),-6) AND | TRUNC(SYSDATE,'MM') | AND c.customer_id NOT IN ( | SELECT s.customer_id | FROM subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND produst_id > 1); | | Actually, I need to do that 3 times with the date ranges being 1-6 | months, 6-12 months & 12-18 months. | | I know I can run 3 queries, pretty simple. But I was wondering if I | could use an analytical function to help group the dates and such. | | So, I'd want a list of the values above for the 3 periods. I'm not | interested in the actual date values, just as long as they fall within | the range. I do not need them broken up by January, February, March, | etc. Just all customers within this 6 month period, then the nextt 6 | months and the last 6 months. | | I just think it'd be cool to see if an analytical function can do that | for me. Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Regards Michel |
1-6 months
6-12 months
12-18 months
I mean, it is not a program with a loop or anything. Maybe some sort
of Pivot table? I've read a bit about those......
-----------------------------------------
Given your queries there is no group only all rows between 2 dates.
Then define what you mean with "group".
Regards
Michel
#12
| |||
| |||
|
|
m... (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5c... (AT) m44g2000hsc (DOT) googlegroups.com... | | Hi, | | I am beginning to like these analytical functions. Especially with | 10g they seem to have a lot of options. Anyhow, I have a simple query | like this: | | SELECT s.customer_id, c.signup_date, s.produst_id, s.status FROM | subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND status > 1 | AND produst_id = 1 | AND start_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),-6) AND | TRUNC(SYSDATE,'MM') | AND c.customer_id NOT IN ( | SELECT s.customer_id | FROM subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND produst_id > 1); | | Actually, I need to do that 3 times with the date ranges being 1-6 | months, 6-12 months & 12-18 months. | | I know I can run 3 queries, pretty simple. But I was wondering if I | could use an analytical function to help group the dates and such. | | So, I'd want a list of the values above for the 3 periods. I'm not | interested in the actual date values, just as long as they fall within | the range. I do not need them broken up by January, February, March, | etc. Just all customers within this 6 month period, then the nextt 6 | months and the last 6 months. | | I just think it'd be cool to see if an analytical function can do that | for me. Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Regards Michel |
1-6 months
6-12 months
12-18 months
I mean, it is not a program with a loop or anything. Maybe some sort
of Pivot table? I've read a bit about those......
-----------------------------------------
Given your queries there is no group only all rows between 2 dates.
Then define what you mean with "group".
Regards
Michel
#13
| |||
| |||
|
|
m... (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5c... (AT) m44g2000hsc (DOT) googlegroups.com... | | Hi, | | I am beginning to like these analytical functions. Especially with | 10g they seem to have a lot of options. Anyhow, I have a simple query | like this: | | SELECT s.customer_id, c.signup_date, s.produst_id, s.status FROM | subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND status > 1 | AND produst_id = 1 | AND start_date BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'MM'),-6) AND | TRUNC(SYSDATE,'MM') | AND c.customer_id NOT IN ( | SELECT s.customer_id | FROM subscriptions s, customers c | WHERE c.customer_id = s.customer_id | AND produst_id > 1); | | Actually, I need to do that 3 times with the date ranges being 1-6 | months, 6-12 months & 12-18 months. | | I know I can run 3 queries, pretty simple. But I was wondering if I | could use an analytical function to help group the dates and such. | | So, I'd want a list of the values above for the 3 periods. I'm not | interested in the actual date values, just as long as they fall within | the range. I do not need them broken up by January, February, March, | etc. Just all customers within this 6 month period, then the nextt 6 | months and the last 6 months. | | I just think it'd be cool to see if an analytical function can do that | for me. Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Regards Michel |
1-6 months
6-12 months
12-18 months
I mean, it is not a program with a loop or anything. Maybe some sort
of Pivot table? I've read a bit about those......
-----------------------------------------
Given your queries there is no group only all rows between 2 dates.
Then define what you mean with "group".
Regards
Michel
#14
| |||
| |||
|
|
"Michel Cadot" <micadot{at}altern{dot}org> wrote: Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Well, I was wondering how to get it into 3 groups: 1-6 months 6-12 months 12-18 months I mean, it is not a program with a loop or anything. *Maybe some sort of Pivot table? *I've read a bit about those...... |
case
when n between 1 and 3 then '1..3'
when n between 4 and 6 then '4..6'
when n between 7 and 9 then '7..9'
end grp
from (select mod(level, 17) n from dual connect by level <= 100)
where n between 1 and 9;
--
Peter
#15
| |||
| |||
|
|
"Michel Cadot" <micadot{at}altern{dot}org> wrote: Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Well, I was wondering how to get it into 3 groups: 1-6 months 6-12 months 12-18 months I mean, it is not a program with a loop or anything. *Maybe some sort of Pivot table? *I've read a bit about those...... |
case
when n between 1 and 3 then '1..3'
when n between 4 and 6 then '4..6'
when n between 7 and 9 then '7..9'
end grp
from (select mod(level, 17) n from dual connect by level <= 100)
where n between 1 and 9;
--
Peter
#16
| |||
| |||
|
|
"Michel Cadot" <micadot{at}altern{dot}org> wrote: Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Well, I was wondering how to get it into 3 groups: 1-6 months 6-12 months 12-18 months I mean, it is not a program with a loop or anything. *Maybe some sort of Pivot table? *I've read a bit about those...... |
case
when n between 1 and 3 then '1..3'
when n between 4 and 6 then '4..6'
when n between 7 and 9 then '7..9'
end grp
from (select mod(level, 17) n from dual connect by level <= 100)
where n between 1 and 9;
--
Peter
#17
| |||
| |||
|
|
"Michel Cadot" <micadot{at}altern{dot}org> wrote: Just use add_months(...,-18) and an ORDER BY start_date and the date will be grouped. Well, I was wondering how to get it into 3 groups: 1-6 months 6-12 months 12-18 months I mean, it is not a program with a loop or anything. *Maybe some sort of Pivot table? *I've read a bit about those...... |
case
when n between 1 and 3 then '1..3'
when n between 4 and 6 then '4..6'
when n between 7 and 9 then '7..9'
end grp
from (select mod(level, 17) n from dual connect by level <= 100)
where n between 1 and 9;
--
Peter
 |
« 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.