is it possible to do this? - 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

is it possible to do this?

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 05:41:09

I have numerous queries all doing a similar thing. What would be nice if i could run them at the same time but getting the 'massive query' returning the opposite result. Hope that makes sense. Here is an example:

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date < DATEADD(DAY, - 42, GETDATE()))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus LIKE '')
AND (NOT (Task LIKE '%x%'))
AND (ExpressEmail LIKE '%@%')
AND (WebSite NOT LIKE '')
AND (Unsubscribe >= 0)
AND (Artwork LIKE '')

SELECT *
FROM dbo.wce_contact
WHERE (Mail2Date < DATEADD(DAY, - 38, GETDATE()))
AND (NOT (Mail1Date IS NULL))
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus LIKE '')
AND (NOT (Task LIKE '%x%'))
AND (ExpressEmail LIKE '%@%')
AND (WebSite NOT LIKE '')
AND (Unsubscribe = 0)
AND (Artwork LIKE '')

Above are two queries doing a similar thing. Is there anyway i can run them both at the same time but saying 'NOT LIKE' this query. This will hopefully reveal some invalid contacts in our database. ive tried to explain it the best i can...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 05:53:49

You want all records from first query that does not exist in second query?

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 05:55:20

I want all records from both queries that dont match the two queries criteria.

A + B = C

i want C not = A + B

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 05:59:23

[code]SELECT *
FROM dbo.wce_contact
WHERE Mail1Date < DATEADD(DAY, - 42, GETDATE())
AND Mail2Date >= DATEADD(DAY, - 38, GETDATE())
AND Mail3Date IS NULL
AND Mail4Date IS NULL
AND Mail5Date IS NULL
AND Mail6Date IS NULL
AND Mail7Date IS NULL
AND Mail8Date IS NULL
AND Mail9Date IS NULL
AND Mail10Date IS NULL
AND Mail11Date IS NULL
AND Mail12Date IS NULL
AND Mail14Date IS NULL
AND Mail15Date IS NULL
AND Mail16Date IS NULL
AND IDStatus = ''
AND NOT Task LIKE '%x%'
AND ExpressEmail LIKE '%@%'
AND WebSite > ''
AND Unsubscribe > 0
AND Artwork = ''[/code]

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 06:20:35

i dont quite understand what you have done there? could you explain your method of thinking?

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 06:32:41

oops i put in the query from the start it is supposed to look like this:

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus LIKE '')
AND (NOT (Task LIKE '%x%'))
AND (ExpressEmail LIKE '%@%')
AND (WebSite NOT LIKE '')
AND (Unsubscribe >= 0)
AND (Artwork LIKE '')

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NOT NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus LIKE '')
AND (NOT (Task LIKE '%x%'))
AND (ExpressEmail LIKE '%@%')
AND (WebSite NOT LIKE '')
AND (Unsubscribe >= 0)
AND (Artwork LIKE '')

SELECT *
FROM dbo.wce_contact
WHERE (Mail1Date IS NOT NULL)
AND (Mail2Date IS NOT NULL)
AND (Mail3Date IS NOT NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus LIKE '')
AND (NOT (Task LIKE '%x%'))
AND (ExpressEmail LIKE '%@%')
AND (WebSite NOT LIKE '')
AND (Unsubscribe >= 0)
AND (Artwork LIKE '')

To me that now reads A + B +C =D

I want D not = A + B + C

Hope this makes more sense now?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 06:41:28

[code]SELECT *
FROM dbo.wce_contact
WHERE Mail1Date IS NOT NULL
AND (
Mail2Date IS NULL
AND Mail3Date IS NULL
OR
Mail2Date IS NOT NULL
AND Mail3Date IS NULL
OR
Mail2Date IS NOT NULL
AND Mail3Date IS NOT NULL
)
AND Mail4Date IS NULL
AND Mail5Date IS NULL
AND Mail6Date IS NULL
AND Mail7Date IS NULL
AND Mail8Date IS NULL
AND Mail9Date IS NULL
AND Mail10Date IS NULL
AND Mail11Date IS NULL
AND Mail12Date IS NULL
AND Mail14Date IS NULL
AND Mail15Date IS NULL
AND Mail16Date IS NULL
AND IDStatus = ''
AND Task LIKE '%[^x]%'
AND ExpressEmail LIKE '%@%'
AND WebSite > ''
AND Unsubscribe >= 0
AND Artwork = ''[/code]

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 06:46:00

I dont think this working properly? please explain what that query is supposed to do?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 06:48:46

The very same thing as your three A, B and C queries.
Instead of having three resultsets, you now have one resultset containing all records from A + B + C.
Now you only have D which is A + B + C.

You write this for yourself in a previous response.

E 12°55'05.25"
N 56°04'39.16"

Topaz
Posting Yak Master

199 Posts

Posted - 2008-05-29 : 07:08:29

Thanks, but i think you have misunderstood what i meant? I want the opposite of what A + B + C produce.

The above (A) queries find all the records where mail1date is not null and mail2date is null mail3,4 etc hence finding all the records with only mail1date filled in.

B will find all the records where mail1 and 2date are not null and the rest are null etc same goes for C

That is what i call neat data their results...However i want to find out the records in my database that dont follow this pattern eg having mail5date filled in but mail2,8 9 are null. See where im going with this??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 07:18:06

No. It is all a mess.
Please read this blog post http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
And when you are done, please this this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-29 : 07:19:11

[code]SELECT *
FROM dbo.wce_contact
WHERE NOT (
Mail1Date IS NOT NULL
AND (
Mail2Date IS NULL
AND Mail3Date IS NULL
OR
Mail2Date IS NOT NULL
AND Mail3Date IS NULL
OR
Mail2Date IS NOT NULL
AND Mail3Date IS NOT NULL
)
AND Mail4Date IS NULL
AND Mail5Date IS NULL
AND Mail6Date IS NULL
AND Mail7Date IS NULL
AND Mail8Date IS NULL
AND Mail9Date IS NULL
AND Mail10Date IS NULL
AND Mail11Date IS NULL
AND Mail12Date IS NULL
AND Mail14Date IS NULL
AND Mail15Date IS NULL
AND Mail16Date IS NULL
AND IDStatus = ''
AND Task LIKE '%[^x]%'
AND ExpressEmail LIKE '%@%'
AND WebSite > ''
AND Unsubscribe >= 0
AND Artwork = ''
)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources