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.
| Author |
Topic |
|
rushy_23
Starting Member
1 Post |
 Posted - 2014-07-27 : 13:49:30
|
| Hi there,New to the forum and also fairly new to SQL.I am trying to create a query which looks up data for the past 30 days from table JeopardyEvents.I have a "where" condition as per below:where Code != 'OR510' AND Code != 'OR520' AND Code != 'OR30' AND Code != 'OR5894' AND Code !='OR5181' AND Code != 'OR5178' AND Code != 'OR5179' AND Code != 'OR5177' AND Code != 'OR5328' AND Code != 'OR2306' AND Code != 'OR5749' AND Code != 'OR9356'AND Code != 'OR1519' AND Code != 'OR5392' AND Code != 'OR5393' AND Code != 'OR540' AND Code != 'OR5185'AND Code != 'OR5184' AND Code != 'OR510' AND Code != 'OR520' AND Code != 'OR530' AND Code !='OR5894' AND Code != 'OR5181' AND Code != 'OR5178' AND Code != 'OR5179' AND Code != 'OR5177' AND Code != 'OR5328' AND Code != 'OR2306' AND Code != 'OR5749' AND Code != 'OR9356' AND Code != 'OR1519' AND Code != 'OR5392' AND Code != 'OR5393' AND Code != 'OR540' AND Code != 'OR5185' AND Code != 'OR5184' AND Code != 'BS001' AND Code != 'BS004'Follow by:and (CreatedOn > '2014-06-10 23:59:57' or ModifiedOn > '2014-06-10 23:59:57' )I want to replace the date range to just pull data from the last 30 days from the time the query was run.I'm using Microsoft SQL Server Management Studio 2012.I can move this request for help into another part of the forum if need be.Ta :)Rush |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-07-27 : 14:31:01
|
| -- All this can be consolidated in an NOT IN Clause: where Code != 'OR510' AND Code != 'OR520' AND Code != 'OR30' AND Code != 'OR5894' AND Code !='OR5181' AND Code != 'OR5178' AND Code != 'OR5179' AND Code != 'OR5177' AND Code != 'OR5328' AND Code != 'OR2306' AND Code != 'OR5749' AND Code != 'OR9356'AND Code != 'OR1519' AND Code != 'OR5392' AND Code != 'OR5393' AND Code != 'OR540' AND Code != 'OR5185'AND Code != 'OR5184' AND Code != 'OR510' AND Code != 'OR520' AND Code != 'OR530' AND Code !='OR5894' AND Code != 'OR5181' AND Code != 'OR5178' AND Code != 'OR5179' AND Code != 'OR5177' AND Code != 'OR5328' AND Code != 'OR2306' AND Code != 'OR5749' AND Code != 'OR9356' AND Code != 'OR1519' AND Code != 'OR5392' AND Code != 'OR5393' AND Code != 'OR540' AND Code != 'OR5185' AND Code != 'OR5184' AND Code != 'BS001' AND Code != 'BS004'Can Be WHERE Code NOT IN ('OR510','OR520','OR30','OR5894','OR5181' ... the rest of you comma separated list followed by End Parenthesis )-- the date portion can be.and (CreatedOn > DATEADD(MM,-1,Getdate()) or ModifiedOn > DATEADD(MM,-1,Getdate()))--OR use a variable at the beginning of your script DECLARE @dt datetime = DATEADD(MM,-1,Getdate()) -- in which case your clause would look like and (CreatedOn > @dt or ModifiedOn > @dt )-- this is works on the idea that the date you are running is 1 month from the last time you ran it. |
 |
|
|
|
|
|
|
|
New To Site / Microsoft SQL Query