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.

Query for no customer activity

2 posts in General Discussion Last posting was on 2011-12-02 14:25:35.0Z
Detox Posted on 2011-08-26 00:41:10.0Z
Sender: 6dd2.4e551f58.1804289383@sybase.com
From: Detox
Newsgroups: ianywhere.public.general
Subject: Query for no customer activity
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e56eba6.6d6c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 25 Aug 2011 17:41:10 -0700
X-Trace: forums-1-dub 1314319270 10.22.241.41 (25 Aug 2011 17:41:10 -0700)
X-Original-Trace: 25 Aug 2011 17:41:10 -0700, 10.22.241.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8150
Article PK: 7169

Hello.

I am attempting to write a query to show those individuals
who have had no activity in the last x days.
It doesn't error, but I do not get the results I need. Here
is the query and excerpt of results. As you can see, I want
to see a list of folks who have not been here in the last 2
months. Instead, it is showing me the opposite.

Any help is appreciated!

SELECT cg.gruppe_name, cg.crecordnumber, cg.client_name,
cg.reason, n.date_service, n.date_creation FROM
client_gruppe cg, notes n WHERE cg.crecordnumber =
n.crecordnumber AND cg.gruppe_name = 'RCE' AND DATE
(n.date_service) >= DATE(DATEADD(DAY, -60, NOW()))
However, the results do not show those who have NO activity
in the last 60 days. Instead, it shows:
gruppe_name crecordnumber client_name reason
date_service
RCE Removed Removed PARKTODD1102
2011-08-25 12:23:41.943
RCE KLUNSTEV0508 2011-08-24
10:25:00.566
RCE PARKTODD1102 2011-08-19
12:32:26.213
RCE PARKTODD1102 2011-08-19
12:32:26.213
RCE PARKTODD1102 2011-08-16
09:17:08.823
RCE PARKTODD1102 2011-08-16
09:17:08.823
RCE PARKTODD1102 2011-08-16
09:17:08.823
RCE PARKTODD1102 2011-08-16
09:13:24.696
RCE PARKTODD1102 2011-08-09
10:41:46.003
RCE PARKTODD1102 2011-08-05
16:46:25.236
RCE PARKTODD1102 2011-08-05
08:43:17.603
RCE PARKTODD1102 2011-08-05
08:43:17.603
RCE PARKTODD1102 2011-08-05
08:43:17.603
RCE KLUNSTEV0508 2011-07-22
14:04:45.986
RCE KLUNSTEV0508 2011-07-22
14:04:45.986
>>>>>>>>>>>>> List continues older data <<<<<<<<<<<<<<<
Can someone explain what I am doing wrong?


rajeev.gautam Posted on 2011-12-02 14:25:35.0Z
Sender: 4973.4ed8d880.1804289383@sybase.com
From: rajeev.gautam@unisys.com
Newsgroups: ianywhere.public.general
Subject: Re: Query for no customer activity
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ed8dfdf.4e76.1681692777@sybase.com>
References: <4e56eba6.6d6c.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 2 Dec 2011 06:25:35 -0800
X-Trace: forums-1-dub 1322835935 10.22.241.41 (2 Dec 2011 06:25:35 -0800)
X-Original-Trace: 2 Dec 2011 06:25:35 -0800, 10.22.241.41
Lines: 59
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:8164
Article PK: 7177

You should the month option to get your desired result
i.e. DATE(DATEADD(MONTH, -2, NOW()))

HTH
Rajeev

> Hello.
>
> I am attempting to write a query to show those individuals
> who have had no activity in the last x days.
> It doesn't error, but I do not get the results I need.
> Here is the query and excerpt of results. As you can see,
> I want to see a list of folks who have not been here in
> the last 2 months. Instead, it is showing me the
> opposite.
>
> Any help is appreciated!
>
> SELECT cg.gruppe_name, cg.crecordnumber, cg.client_name,
> cg.reason, n.date_service, n.date_creation FROM
> client_gruppe cg, notes n WHERE cg.crecordnumber =
> n.crecordnumber AND cg.gruppe_name = 'RCE' AND DATE
> (n.date_service) >= DATE(DATEADD(DAY, -60, NOW()))
> However, the results do not show those who have NO
> activity in the last 60 days. Instead, it shows:
> gruppe_name crecordnumber client_name reason
> date_service
> RCE Removed Removed PARKTODD1102
> 2011-08-25 12:23:41.943
> RCE KLUNSTEV0508
> 2011-08-24 10:25:00.566
> RCE PARKTODD1102
> 2011-08-19 12:32:26.213
> RCE PARKTODD1102
> 2011-08-19 12:32:26.213
> RCE PARKTODD1102
> 2011-08-16 09:17:08.823
> RCE PARKTODD1102
> 2011-08-16 09:17:08.823
> RCE PARKTODD1102
> 2011-08-16 09:17:08.823
> RCE PARKTODD1102
> 2011-08-16 09:13:24.696
> RCE PARKTODD1102
> 2011-08-09 10:41:46.003
> RCE PARKTODD1102
> 2011-08-05 16:46:25.236
> RCE PARKTODD1102
> 2011-08-05 08:43:17.603
> RCE PARKTODD1102
> 2011-08-05 08:43:17.603
> RCE PARKTODD1102
> 2011-08-05 08:43:17.603
> RCE KLUNSTEV0508
> 2011-07-22 14:04:45.986
> RCE KLUNSTEV0508
> 2011-07-22 14:04:45.986
> >>>>>>>>>>>>> List continues older data <<<<<<<<<<<<<<<
> Can someone explain what I am doing wrong?