Re: Query

<mike_at_adlineservices.com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5cec4_at_m44g2000hsc.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 Received on Sat Sep 27 2008 - 01:16:35 CDT