Extended Join Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Extended Join Problem

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-04 : 16:52:01

Good afternoon, I have a working query that I need to add one more piece to: Here is the working query

select ltrim(rtrim(b.client_code))+ ltrim(rtrim(b.engagement)) as EngNum,b.client_code,
b.engagement,b.accountid,CONVERT(VARCHAR(10),b.modifydate,101) as bMaxDT,b.entity_type,b.opportunityid,
c.account,h.opportunityid,CONVERT(VARCHAR(10),h.MaxDT,101)
from saleslogix.sysdba.c_acct_Engagement B

-- select c.accountid,c.account,CONVERT(VARCHAR(10),h.MaxDT,101) as MaxDate,d.relationship,

left outer join saleslogix.sysdba.Account C ON c.accountid = b.accountid
left outer join saleslogix.sysdba.C_Account_Ext D ON d.accountid = c.accountid
left outer join saleslogix.sysdba.C_ACCTS_MARKETING_100 E ON e.accountid = b.accountid
left outer join saleslogix.sysdba.C_Private_Equity F ON f.accountid = b.accountid
left outer join saleslogix.sysdba.C_Account_Ext2 G ON g.accountid = b.accountid

inner join (select accountid,opportunityid,max(modifydate) as MaxDT from saleslogix.sysdba.Opportunity
group by AccountID,opportunityID) H
ON h.accountid = b.accountid

WHERE

d.Relationship='Client' and
e.accountid is null and
f.accountid is null and
g.accountid is null and
h.MaxDt < '2009-01-01 00:00:00.00'

and
--
ltrim(rtrim(B.Client_Code)) + ltrim(rtrim(B.Engagement))
not in (select ltrim(rtrim(cltnum)) + ltrim(rtrim(clteng))
-- in (select ltrim(rtrim(cltnum)) + ltrim(rtrim(clteng))
from [rl-sql02].jhcohn_sql.dbo.clients)
order by EngNum DESC

..............................

As you will notice above that table B and Table H are related.
I need to now add logic that will EXCLUDE any records from Table H where the opportunityID's match and the modifydate from Table H is greater than the modifydate from Table B

This already gave me a headache....not sure which way to go.

Thanks

Bryan Holmstrom

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-04 : 17:25:05

Have you tried adding this to the ON clause for the table "h" join criteria?


inner join (select accountid,opportunityid,max(modifydate) as MaxDT from saleslogix.sysdba.Opportunity
group by AccountID,opportunityID) H
       ON      h.accountid = b.accountid 
       and not (h.opportunityid = b.opportunityid and h.MaxDt > b.modifydate)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources