Re: Query

<mike_at_adlineservices.com> a écrit dans le message de news: 5df8b6b1-f4a5-48d6-b336-4098fcb26295_at_26g2000hsk.googlegroups.com... On Sep 27, 1:16 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <m..._at_adlineservices.com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5c..._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

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......

Given your queries there is no group only all rows between 2 dates. Then define what you mean with "group".

Regards
Michel Received on Sat Sep 27 2008 - 15:27:15 CDT