11/25/2024 11:39:38 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss reports for SalesLogix including Crystal, SQL Reports, etc. View the code of conduct for posting guidelines.
|
|
|
| |
|
Re: No History for No Activity for a perticular date range
Posted: 10 May 07 7:24 AM
|
fiogf49gjkf0d You just need to base your report on a query along the lines of
select [account information] from account
where accountid not in
(select accountid from activity where startdate between [your date range]
union
select accountid from history where completeddate between [your date range])
That should be enough to get your started...
ws |
|
|
|
Re: No History for No Activity for a perticular date range
Posted: 10 May 07 8:41 AM
|
fiogf49gjkf0d It's a little more complicated than that, I had this same need. I created a view in sql and then ran my report against that, this view looks at the history table and attachment table to verify that neither has been updated in 90 days.
SELECT distinct ACCOUNTID as a_ACCOUNTID , account
from SYSDBA.ACCOUNT
where
(
ACCOUNTID in (SELECT distinct att.[ACCOUNTID] as att_ACCOUNTID
fROM [SalesLogix].[SYSDBA].[ATTACHMENT] att
where ATTACHDATE < getdate()-90
and [ACCOUNTID] not in ( select [ACCOUNTID] from
[SalesLogix].[SYSDBA].[ATTACHMENT]
where ATTACHDATE >= getdate()-90
AND ACCOUNTID IS NOT NULL))
or
ACCOUNTID in ( select at_ACCOUNTID
from ( select distinct att.[ACCOUNTID] att_ACCOUNTID, at.[ACCOUNTID] as at_ACCOUNTID
from [SalesLogix].[SYSDBA].[ATTACHMENT] att , [SalesLogix].SYSDBA.ACCOUNT at
where att.[ACCOUNTID] =* at.[ACCOUNTID] ) XX
where att_ACCOUNTID is null
and at_ACCOUNTID is not null )
)
and
( ACCOUNTID in (SELECT distinct hst.[ACCOUNTID] as hst_ACCOUNTID
fROM [SalesLogix].[SYSDBA].[HISTORY] hst
where [MODIFYDATE] < getdate()-90
and [ACCOUNTID] not in ( select [ACCOUNTID] from
[SalesLogix].[SYSDBA].[HISTORY]
where [MODIFYDATE] >= getdate()-90
AND ACCOUNTID IS NOT NULL))
or
ACCOUNTID in (select at_ACCOUNTID as hst_ACCOUNTID
from ( select distinct hst.[ACCOUNTID] hst_ACCOUNTID, at.[ACCOUNTID] as at_ACCOUNTID
from [SalesLogix].[SYSDBA].[HISTORY] hst , [SalesLogix].SYSDBA.ACCOUNT at
where hst.[ACCOUNTID] =* at.[ACCOUNTID] ) XX
where hst_ACCOUNTID is null
and at_ACCOUNTID is not null )
)
and CREATEDATE < getdate() -90 |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|