adding another table - 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 2008 Forums

Transact-SQL (2008)

adding another table

Author

Topic

aciccia
Starting Member

8 Posts

Posted - 2011-07-31 : 19:03:41

I'm trying to add the last paragraph into the query. The query runs but it is ignoring the last part.

Thank you,

select *

from Arr_ANALYTICS.dbo.detail d

Inner join Arr_analytics.dbo.detail di

on d.SD_KEY=di.SD_KEY

where d.SD_TRAN_CDE_CAT in ('31')

and d.SD_BEN_WIR_ACCT_IDENT not like ('%twr%')

and d.SD_TERM_NAME_LOC = di.SD_TERM_NAME_LOC

and d.md_tran_amt1 > 4999

and di.SD_Tran_cde_cat in ('31')

and di.SD_BEN_WIR_ACCT_IDENT not like ('%twr%')

and di.md_tran_amt1 > 4999

AND di.DD_DATE BETWEEN DATEADD(minute, -10, d.DD_DATE) AND DATEADD(second, -5, d.DD_DATE)

AND EXISTS (Select SD_DATEKEY from Arr_Analytics.dbo.Detail di1

WHERE di.SD_KEY = di1.SD_KEY

and di1.SD_TRAN_CDE_CAT in ('21')

and di1.md_tran_amt1 > 10000

AND di1.DD_DATE BETWEEN DATEADD(day, -3, di.DD_DATE) AND DATEADD(second, -5, di.DD_DATE))

And EXISTS (SELECT di1.SD_KEY, di1.SD_DATEKEY

FROM Arr_Analytics.dbo.Detail di1

JOIN Arr_Analytics.dbo.Master m

ON di1.SD_KEY = m.SM_KEY

WHERE m.DM_ACCT_OPN_DAT between '06/01/2011' and '07/27/2011')

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-31 : 19:07:42

maybe
And EXISTS (SELECT di1.SD_KEY, di1.SD_DATEKEY
from Arr_Analytics.dbo.Master m
ON di.SD_KEY = m.SM_KEY
WHERE m.DM_ACCT_OPN_DAT between '06/01/2011' and '07/27/2011')

Not really sure what you want but your subquery wasn't correlated and so would either not affect the result or cause an empty resutset.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aciccia
Starting Member

8 Posts

Posted - 2011-07-31 : 19:11:55

After making the change, I now get this message:
"Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'ON'."

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-31 : 19:12:46

sorry

And EXISTS (SELECT di1.SD_KEY, di1.SD_DATEKEY
from Arr_Analytics.dbo.Master m
where di.SD_KEY = m.SM_KEY
and m.DM_ACCT_OPN_DAT between '06/01/2011' and '07/27/2011')

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

aciccia
Starting Member

8 Posts

Posted - 2011-07-31 : 19:16:06

Now its reading:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Arr_Analytics.dbo.Master'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources