Where exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Where exists

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-07-15 : 10:59:05

I am trting to add an exists to a current stored procedure. I cannot get the correct syntax.

select '06/30/2010' as AsOfDate,
N.ID,
N.FIRST_NAME,
N.MIDDLE_NAME,
N.LAST_NAME,
N.TITLE,
A.ADDRESS_1,
A.ADDRESS_2,
A.CITY,
A.STATE_PROVINCE,
A.ZIP,
A.COUNTRY,
N.WORK_PHONE,
N.FAX,
N.EMAIL,
N.JOIN_DATE,
N.PAID_THRU,
N.CHAPTER as Old_CHAPTER,
I.CHAPTERS_JOINED,
C.PRIMARY_FUNCTION,
C.EXPERTISE_AREA,
C.INDUST_FAMILIAR

from Name N, Name_Address A, CompProfile C, Mem_Info I
where N.ID = C.ID and
N.ID = I.ID and
N.MEMBER_TYPE in ('M', 'NM') and
N.Paid_thru > = DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 13, 0)) and
N.MAIL_ADDRESS_NUM = A.ADDRESS_NUM and
N.STATUS = 'A'

************

where exists
(select max (a.thru_date), N.id
FROM Name n
INNER JOIN activity a
ON N.ID = A.ID
where a.thru_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
and and a.product_code like 'chapt%'
group by N.id)

I looked through the forum, but cannot find an example similar. The exists captures only data from before the current month begin date. Everthign about has to do with data in their data base regardless of date. The Activity table can contain many line items per person and I only want to compare off the last chapter activity record.

Thanks!
DZ

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-15 : 11:02:57

...
N.STATUS = 'A'
AND EXISTS(select * from ...)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources