dbTalk Databases Forums  

Query

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc Query

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss Query in the comp.databases.oracle.misc forum.



Reply
Page 1 of 2 1 2 >
 
Thread Tools Display Modes
  #1  
Old   
mike@adlineservices.com
 
Posts: n/a

Default Query - 09-27-2008 , 01:03 AM






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.

Reply With Quote
  #2  
Old   
Michel Cadot
 
Posts: n/a

Default Re: Query - 09-27-2008 , 01:16 AM






<mike (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5cec4...oglegroups.com...
Quote:
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




Reply With Quote
  #3  
Old   
Michel Cadot
 
Posts: n/a

Default Re: Query - 09-27-2008 , 01:16 AM



<mike (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5cec4...oglegroups.com...
Quote:
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




Reply With Quote
  #4  
Old   
Michel Cadot
 
Posts: n/a

Default Re: Query - 09-27-2008 , 01:16 AM



<mike (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5cec4...oglegroups.com...
Quote:
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




Reply With Quote
  #5  
Old   
Michel Cadot
 
Posts: n/a

Default Re: Query - 09-27-2008 , 01:16 AM



<mike (AT) adlineservices (DOT) com> a écrit dans le message de news: f13b332c-a1f1-4d37-ba6f-0a24d6a5cec4...oglegroups.com...
Quote:
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




Reply With Quote
  #6  
Old   
mike@adlineservices.com
 
Posts: n/a

Default Re: Query - 09-27-2008 , 02:31 PM


On Sep 27, 1:16*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote:
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
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......



Reply With Quote
  #7  
Old   
mike@adlineservices.com
 
Posts: n/a

Default Re: Query - 09-27-2008 , 02:31 PM


On Sep 27, 1:16*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote:
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
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......



Reply With Quote
  #8  
Old   
mike@adlineservices.com
 
Posts: n/a

Default Re: Query - 09-27-2008 , 02:31 PM


On Sep 27, 1:16*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote:
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
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......



Reply With Quote
  #9  
Old   
mike@adlineservices.com
 
Posts: n/a

Default Re: Query - 09-27-2008 , 02:31 PM


On Sep 27, 1:16*am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote:
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
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......



Reply With Quote
  #10  
Old   
Michel Cadot
 
Posts: n/a

Default Re: Query - 09-27-2008 , 03:27 PM



<mike (AT) adlineservices (DOT) com> a écrit dans le message de news: 5df8b6b1-f4a5-48d6-b336-4098fcb26295...oglegroups.com...
On Sep 27, 1:16 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Quote:
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
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





Reply With Quote
Reply
Page 1 of 2 1 2 >

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top