Re: Query

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...... Received on Sat Sep 27 2008 - 14:31:20 CDT