Query

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. Received on Sat Sep 27 2008 - 01:03:27 CDT