SQL Query Exclude Help - 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

SQL Query Exclude Help

Author

Topic

payners
Starting Member

4 Posts

Posted - 2012-05-10 : 22:32:57

All,

First time poster on here. I am trying to create a query where I want to exclude data between a date range but also having certain codes.

Date:

(substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) not between '2012-05-08%' and '2012-05-10%

Status Code:

statuscode NOT IN ('58','59'))

What would my statement look like to exclude data that meets BOTH of those conditions? Everything I do excludes all in that date range and all in the status code range.

Thanks in advance. SQL newbie but learning :).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:37:15

[code]
...
WHERE statuscode NOT IN ('58','59')
AND UTCBigintToUTCTime(starttime) >= '2012-05-08'
AND UTCBigintToUTCTime(starttime) < '2012-05-11'
[/code]

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

payners
Starting Member

4 Posts

Posted - 2012-05-10 : 22:40:15

Thanks for the reply visakh16 but that will exclude all '58' and '59' code and anything in that date range separately won't it? I need some that exclude status code 58 and 59 only within that date range.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:48:30

quote:
Originally posted by payners

Thanks for the reply visakh16 but that will exclude all '58' and '59' code and anything in that date range separately won't it? I need some that exclude status code 58 and 59 only within that date range.

can you explain with sample data what you want?

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

payners
Starting Member

4 Posts

Posted - 2012-05-10 : 22:57:40

Here is an example of what i was trying. Note the "Excludes" I have in the where. I need to exlude data that matches both of those conditions not just one or the other.

SELECT

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) as 'Date',
count(distinct domain_jobarchive.id) as Jobs,
(AVG((CASE statuscode WHEN 0 THEN 1 when 1 then 1 ELSE 0 END))*100) as Rate

FROM

domain_JobArchive

WHERE

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) > '2011-12-31%' and
type IN ('0','1') and
// Excludes
(substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) not between '2012-05-08%' and '2012-05-10%' and statuscode NOT IN ('58','59'))
// End of Excludes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 22:59:07

please post sample data and explain what you want. Cant make out from query what you're looking at as that itself is not working one!

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

payners
Starting Member

4 Posts

Posted - 2012-05-10 : 23:06:14

Sorry about that..took out the group by :

SELECT

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) as 'Date',
count(distinct domain_jobarchive.id) as Jobs,
(AVG((CASE statuscode WHEN 0 THEN 1 when 1 then 1 ELSE 0 END))*100) as Rate

FROM

domain_JobArchive

WHERE

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) > '2011-12-31%' and
type IN ('0','1') and
// Excludes
(substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11) not between '2012-05-08%' and '2012-05-10%' and statuscode NOT IN ('58','59'))
// End of Excludes

GROUP BY

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11)

ORDER BY

substring(CAST(UTCBigintToUTCTime(starttime) as varchar(19)),0,11)

Sample Output:

Date,Jobs,Rate
'2012-01-01',3188,96.235885
'2012-01-02',5406,98.002220
'2012-01-03',6773,98.671194

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 23:12:35

see how to post proper data and output required. Unless you do that I'm not going to do any guess work

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources