Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Help with subquery

3 posts in General Discussion Last posting was on 2010-04-15 19:30:34.0Z
"Manuel Espinoza" <jmespinoza_no_spam Posted on 2010-04-14 16:53:14.0Z
From: "Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx>
Newsgroups: sybase.public.ase.general
Subject: Help with subquery
Lines: 38
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc5f2fa$1@forums-1-dub>
Date: 14 Apr 2010 09:53:14 -0700
X-Trace: forums-1-dub 1271263994 10.22.241.152 (14 Apr 2010 09:53:14 -0700)
X-Original-Trace: 14 Apr 2010 09:53:14 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29127
Article PK: 78364

Hi all!

Sorry if this isn't the correct forum...

I need help with a query. I'm on ASE 15.0.2.

Lets say I have a table "A" like this:

ID exp_datetime cust_id
99 1/26/2009 9
100 2/12/2009 18
101 3/4/2009 11
102 3/5/2009 9
103 4/3/2009 18
104 4/3/2009 19
105 5/3/2009 18
....

I have a query that give me a grouped list from this table, which is OK. But
now, in that same list, I need to know how many months where registered ...
I guess I need a subquery here, but I don't have idea how to do it. I need a
result like this:

Cust_id months_registered
9 2
11 1
18 3
19 1


Hope it's clear my question...



Thanks in advance!


Leonid Gvirtz Posted on 2010-04-14 17:24:23.0Z
From: Leonid Gvirtz <lgvirtz@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Help with subquery
References: <4bc5f2fa$1@forums-1-dub>
In-Reply-To: <4bc5f2fa$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc5fa47@forums-1-dub>
Date: 14 Apr 2010 10:24:23 -0700
X-Trace: forums-1-dub 1271265863 10.22.241.152 (14 Apr 2010 10:24:23 -0700)
X-Original-Trace: 14 Apr 2010 10:24:23 -0700, vip152.sybase.com
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29128
Article PK: 78365

Hi Manuel

There are, probably, a number of ways to achieve what you are looking
for, see the simplest one below:

select cust_id,
count(distinct convert(varchar(4), datepart(year, exp_datetime))
+ convert(varchar(2), datepart(month, exp_datetime))) months_registered
from ta
group by cust_id
order by cust_id
go

Hope it helps
Leonid Gvirtz
http://www.gvirtz-consulting.com

On 4/14/2010 7:53 PM, Manuel Espinoza wrote:
> Hi all!
>
> Sorry if this isn't the correct forum...
>
> I need help with a query. I'm on ASE 15.0.2.
>
> Lets say I have a table "A" like this:
>
> ID exp_datetime cust_id
> 99 1/26/2009 9
> 100 2/12/2009 18
> 101 3/4/2009 11
> 102 3/5/2009 9
> 103 4/3/2009 18
> 104 4/3/2009 19
> 105 5/3/2009 18
> ....
>
> I have a query that give me a grouped list from this table, which is OK. But
> now, in that same list, I need to know how many months where registered ...
> I guess I need a subquery here, but I don't have idea how to do it. I need a
> result like this:
>
> Cust_id months_registered
> 9 2
> 11 1
> 18 3
> 19 1
>
>
> Hope it's clear my question...
>
>
>
> Thanks in advance!
>
>
>
>


Sherlock, Kevin [TeamSybase] Posted on 2010-04-15 19:30:34.0Z
From: "Sherlock, Kevin [TeamSybase]" <kevin.sherlock@teamsybase.com>
Newsgroups: sybase.public.ase.general
References: <4bc5f2fa$1@forums-1-dub>
Subject: Re: Help with subquery
Lines: 49
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bc7695a$1@forums-1-dub>
Date: 15 Apr 2010 12:30:34 -0700
X-Trace: forums-1-dub 1271359834 10.22.241.152 (15 Apr 2010 12:30:34 -0700)
X-Original-Trace: 15 Apr 2010 12:30:34 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29133
Article PK: 78371

select cust_id,
count( distinct datediff(month,'1/1/1900',exp_datetime) ) as
"distinct_months"
from ta
group by cust_id

"Manuel Espinoza" <jmespinoza_no_spam@seicom_dot_com_dot_mx> wrote in
message news:4bc5f2fa$1@forums-1-dub...
> Hi all!
>
> Sorry if this isn't the correct forum...
>
> I need help with a query. I'm on ASE 15.0.2.
>
> Lets say I have a table "A" like this:
>
> ID exp_datetime cust_id
> 99 1/26/2009 9
> 100 2/12/2009 18
> 101 3/4/2009 11
> 102 3/5/2009 9
> 103 4/3/2009 18
> 104 4/3/2009 19
> 105 5/3/2009 18
> ....
>
> I have a query that give me a grouped list from this table, which is OK.
> But now, in that same list, I need to know how many months where
> registered ... I guess I need a subquery here, but I don't have idea how
> to do it. I need a result like this:
>
> Cust_id months_registered
> 9 2
> 11 1
> 18 3
> 19 1
>
>
> Hope it's clear my question...
>
>
>
> Thanks in advance!
>
>
>