Really simple query needs another field

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

Really simple query needs another field

Author

Topic

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 06:06:07

I have written this query in SQL

select * from wce_contact
where (Mail3Date <= '2007-09-25')
AND NOT (Mail1Date is NULL )
AND NOT (Mail2Date 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 (Mail13Date is NULL )
AND (Mail14Date is NULL )
AND (Mail15Date is NULL )
AND (Mail16Date is NULL)

Little bit of background info - We have a database of over 500k websites with email addresses. We use email marketing to contact these sites and everytime they get mailed i need to fill in the MailDate field. Obv Mail5Date means they have been emailed 5 times etc The above qry is trying to find all contacts that have been mailed 3 times and havent been mailed in the last 42 days.

I also have an IDStatus field that allows me not to use the contacts that have opted out our mailing or website shutdown etc I try adding this to the bottom of the query

AND (IDstatus is Null)

But the query runs and finds 0 contacts which i know isnt the case, as when i use our front end database application it produces results.

I was wondering if anybody could possibly help me?

Thanks!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-05 : 06:50:16

Check the values in IDstatus. should you check for an empty string, 0, 'N'?

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

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 06:54:20

My IDStatus field either contains text or nothing. That is why i was using null.

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 06:56:01

quote:
Originally posted by Topaz

My IDStatus field either contains text or nothing. That is why i was using null.

In fact just checking the field values... it either has text, null or is blank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-05 : 07:03:48

[code]SELECT *
FROM wce_contact
WHERE COALESCE(Mail1Date, '') > ''
AND COALESCE(Mail2Date, '') > ''
AND COALESCE(Mail3Date, '') <= '20070925'
AND COALESCE(Mail4Date, '') = ''
AND COALESCE(Mail5Date, '') = ''
AND COALESCE(Mail6Date, '') = ''
AND COALESCE(Mail7Date, '') = ''
AND COALESCE(Mail8Date, '') = ''
AND COALESCE(Mail9Date, '') = ''
AND COALESCE(Mail10Date, '') = ''
AND COALESCE(Mail11Date, '') = ''
AND COALESCE(Mail12Date, '') = ''
AND COALESCE(Mail13Date, '') = ''
AND COALESCE(Mail14Date, '') = ''
AND COALESCE(Mail15Date, '') = ''
AND COALESCE(Mail16Date, '') = ''[/code]

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

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 07:10:52

quote:

Originally posted by Peso

SELECT	*
FROM	wce_contact
WHERE	COALESCE(Mail1Date, '') > ''
	AND COALESCE(Mail2Date, '') > ''
	AND COALESCE(Mail3Date, '') <= '20070925'
	AND COALESCE(Mail4Date, '') = ''
	AND COALESCE(Mail5Date, '') = ''
	AND COALESCE(Mail6Date, '') = ''
	AND COALESCE(Mail7Date, '') = ''
	AND COALESCE(Mail8Date, '') = ''
	AND COALESCE(Mail9Date, '') = ''
	AND COALESCE(Mail10Date, '') = ''
	AND COALESCE(Mail11Date, '') = ''
	AND COALESCE(Mail12Date, '') = ''
	AND COALESCE(Mail13Date, '') = ''
	AND COALESCE(Mail14Date, '') = ''
	AND COALESCE(Mail15Date, '') = ''
	AND COALESCE(Mail16Date, '') = ''

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

That doesn't have the same effect as the query I wrote out.

It seems to turn up a lot more results than expected. What is the COALESCE about? Im a novice...

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 07:22:02

I've ended up having a query looking like this...

select * from wce_contact
where (Mail3Date <= '2007-09-25')
AND NOT (Mail1Date is NULL )
AND NOT (Mail2Date 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 (Mail13Date is NULL )
AND (Mail14Date is NULL )
AND (Mail15Date is NULL )
AND (Mail16Date is NULL)
AND (IDStatus LIKE '')
AND NOT (Task LIKE '%x%')
AND NOT (ExpressEmail LIKE '')

It seems to be working fine. Thanks for all your help.

Next step

This qry returns 22k contacts i need to split the results up into 'bursts' of 3500 is this possible?

A burts is simply 3500 contacts that i email at once.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-05 : 09:53:57

set rowcount 3500
select * from xyz

or

select top 3500 * from xyz
order by x

then repeat after the mailing process has executed.

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:01:25

would i simply attach this to the bottom of the query? Also what happens to the rest of the contacts if 25000 were found? As i need the query to run and then split it into groups of 3500

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 10:05:49

if you are using SQL Server 2005, use the row_number() function

KH
[spoiler]Time is always against us[/spoiler]

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:06:43

not sure how to use the row_number command, would you be able to tell me where i put in my written qry above? thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 10:12:45

are you using SQL Server 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:20:43

yes...

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-05 : 10:24:10

I like the way you have your data normalized

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:26:27

hmm, what does 'normalized' data look like? Am I doing something wrong??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-05 : 10:40:13

[code]select *
from
(
select *,
row_no = row_number() over (order by somecol)
from wce_contact
) a
where a.row_no <= 3500[/code]

see
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

KH
[spoiler]Time is always against us[/spoiler]

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:53:05

i see...

However i cant seem to work it into my query:

select * from wce_contact
where (Mail3Date <= '2007-09-25')
AND NOT (Mail1Date is NULL )
AND NOT (Mail2Date 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 (Mail13Date is NULL )
AND (Mail14Date is NULL )
AND (Mail15Date is NULL )
AND (Mail16Date is NULL)
AND (IDStatus LIKE '')
AND NOT (Task LIKE '%x%')
AND NOT (ExpressEmail LIKE '')

After it selects the first 3500 what happens to the others that it has found?

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 10:58:14

Let's say your business rules change a year from now (and you've been done with this particular project for say 8 months) and they now want to be able to send out 30 mails instead of 16. Now you have to come back to revisit it when you havn't messed with it for 8 months. And to accomplish that, you would have to add 14 more fields to your table (MailDate17...MailDate30). And let's now say you have 20 or so reports built off of that table. You'd need to modify all your reports so they'd have the new fields. You'd have to modify any apps that use the table so they'd have the new fields. So on and so fourth...a big project to change it to allow up to 30 mails instead of 16.

But what if you had a Mail table with something like ID, MailID, and Date. ID would be the link back to wce_contact. Your data would look something like this:

ID MailID Date
1 1 5/1/06
1 2 5/9/06
1 3 5/16/06
5 1 5/2/06
5 2 6/7/06
5 3 6/20/06
5 4 7/12/06

In other words contact 1 has 3 mails and contact 5 has 4 mails. You could have as many mails as you want for any contact. There would be no structure or code changes to make it allow up to 30 mails. Your table isn't growing horizotally (more fields). This sturcture is more normalized. You basically wouldn't have to do anything to make it have the ability to send 30 mails. There would be no report, code, or app changes either. You wouldn't have to come back and revisit something that could have been years before you worked on it. Set it up right the first time. And if you try to go to a normalized structure late in the game when you have alot of data already in use, it will be much harder to convert the existing data into that structure than to just set it up right in the first place.

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 10:59:03

quote:
Originally posted by khtan

select *
from
(
select *,
row_no = row_number() over (order by somecol)
from wce_contact
) a
where a.row_no <= 3500

see
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

KH
[spoiler]Time is always against us[/spoiler]

I got this working, but i find that it produces two sets of results... one with 3500 contacts it V GOOD! the other with the enire result. wondering if it can seperate the entire result into groups of 3500??

Thanks!

Topaz
Posting Yak Master

199 Posts

Posted - 2007-11-05 : 11:04:06

quote:
Originally posted by Van

Let's say your business rules change a year from now (and you've been done with this particular project for say 8 months) and they now want to be able to send out 30 emails instead of 16. To accomplish that, you would have to add 14 more fields to your table (MailDate17...MailDate30). And let's now say you have 20 or so reports built off of that table. You'd need to modify all your reports so they'd have the new fields. You'd have to modify any apps that use the table so they'd have the new fields. So on and so fourth...a big project to change it to allow up to 30 emails instead of 16.

But what if you had a Mail table with something like ID, MailID, and Date. ID would be the link back to wce_contact. Your data would look something like this:

ID MailID Date
1 1 5/1/06
1 2 5/9/06
1 3 5/16/06
5 1 5/2/06
5 2 6/7/06
5 3 6/20/06
5 4 7/12/06

In other words contact 1 has 3 mails and contact 5 has 4 mails. You could have as many mails as you want for any contact. There would be no structure or code changes to make it happen. Your table isn't growing horizotally (more fields). This sturcture is more normalized.

I see where you are coming from, but the reason why we have chosen to go with the horixontal expanding is becasue we simply have copied the structure of Symantec ACT (really old database program)

Its just what we are used to and we like to display all the other email dates and titles etc everytime we click on a contact. So I do agree with what you are saying...It would be implementing it that would actually hurt my head...

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-05 : 11:07:41

Yea, I know what you mean and where you are coming from. That's what X002548 was talking about when he made the comment about how your data was normalized.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources