Help with filter - 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

Help with filter

Author

Topic

dibblejon
Starting Member

36 Posts

Posted - 2012-04-27 : 04:53:48

Hi

I would like to exclude from my query below rows that have the text 'Exclude' in the Exclude field - i have tried <>'Exclude' and not like 'Exclude' - but no data gets returned - where am I going wrong?

SELECT        dbo.CALLS.INITIAL, dbo.CALLS.CALLTYPE, dbo.CALLS.CALLFROM, dbo.CALLS.CALLTO, dbo.CALLS.TALK, DATEADD(dd, 0, DATEDIFF(dd, 0, 
                         dbo.CALLS.CALLDATEANDTIME)) AS Date, dbo.InboundName.Name AS InboundCall, dbo.OutboundName.Name AS OutboundCall, dbo.TMExclude.EXCLUDE, 
                         dbo.CALLS.PHONE
FROM            dbo.CALLS LEFT OUTER JOIN
                         dbo.TMExclude ON dbo.CALLS.PHONE COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.TMExclude.PHONE LEFT OUTER JOIN
                         dbo.OutboundName ON dbo.CALLS.CALLFROM = dbo.OutboundName.CALLFROM COLLATE Latin1_General_CI_AS LEFT OUTER JOIN
                         dbo.InboundName ON dbo.CALLS.CALLTO = dbo.InboundName.CALLTO COLLATE Latin1_General_CI_AS
WHERE        (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.CALLS.CALLDATEANDTIME)) >= CONVERT(DATETIME, '2012-04-01 00:00:00', 102)) AND (dbo.CALLS.CALLTYPE <> 3) AND 
                         (dbo.CALLS.TALK >= 10)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-27 : 04:56:39

[code]WHERE ...
AND character.dbo.message_tbl.id NOT LIKE '%Exclude%'[/code]

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

dibblejon
Starting Member

36 Posts

Posted - 2012-04-27 : 04:59:13

Thanks - have changed query to

SELECT        dbo.CALLS.INITIAL, dbo.CALLS.CALLTYPE, dbo.CALLS.CALLFROM, dbo.CALLS.CALLTO, dbo.CALLS.TALK, DATEADD(dd, 0, DATEDIFF(dd, 0, 
                         dbo.CALLS.CALLDATEANDTIME)) AS Date, dbo.InboundName.Name AS InboundCall, dbo.OutboundName.Name AS OutboundCall, dbo.TMExclude.EXCLUDE, 
                         dbo.CALLS.PHONE
FROM            dbo.CALLS LEFT OUTER JOIN
                         dbo.TMExclude ON dbo.CALLS.PHONE COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.TMExclude.PHONE LEFT OUTER JOIN
                         dbo.OutboundName ON dbo.CALLS.CALLFROM = dbo.OutboundName.CALLFROM COLLATE Latin1_General_CI_AS LEFT OUTER JOIN
                         dbo.InboundName ON dbo.CALLS.CALLTO = dbo.InboundName.CALLTO COLLATE Latin1_General_CI_AS
WHERE        (DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.CALLS.CALLDATEANDTIME)) >= CONVERT(DATETIME, '2012-04-01 00:00:00', 102)) AND (dbo.CALLS.CALLTYPE <> 3) AND 
                         (dbo.CALLS.TALK >= 10) AND (NOT (dbo.TMExclude.EXCLUDE LIKE '%Exclude%'))

But now no data returns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-27 : 15:56:11

do you've any rows satisfying the condition present in table? ie without Exclude word inside EXCLUDE field? what are values it contain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources