| Author |
Topic |
|
blackX
Posting Yak Master
102 Posts |
 Posted - 2007-09-20 : 16:42:10
|
| Hello I first off would like to thank everyone here for this forum has helped me many times just by doing google searches. I am fairly new to SQL and have a problem. I work for a marketing company and as part of that me mail our members. We have 5 letter fields (letter1date, letter2date, letter3date, letter4date, letter4date) in our member table. I would like to write a stored procedure, or atleast a query, that would populate these fields. Example, if letter1date is null or 1-1-1900 set it equal to todays date else if letter2date ... else set todays date to letter5date. The problem is I want to be able to do this for the whole market (area where member lives) and another field called contactstatus at once. Thanks for any help |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-20 : 17:03:29
|
| Perhaps something like this:Update yourtableSet letter1date = Case when letter1date is null then Getdate() when letter1date ='1-1-1900' Then Getdate() Else letter1date endSet letter2date = Case when letter2date is null then Getdate() when letter2date ='1-1-1900' Then Getdate() Else letter2date end...Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-20 : 17:04:14
|
| Basically you will end up with either a valid existing date or Getdate in each of those columns. Is that what you are trying to do?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
blackX
Posting Yak Master
102 Posts |
Posted - 2007-09-20 : 17:27:49
|
| i want it to put it in letter1date if blank or 1-1-1900 (meaning we have never sent this member mail), if there is a valid date in letter1date then i want it to look at letter2date, if letter2date is valid then look at letter3date and so ultimately if all fields are valid then overright letter5date. by valid date i mean any date this is not null or 1-1-1900. All of this would be contingent on the marketgroup and contactstatus fields. did I clarify this for you? thanks |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-09-20 : 17:41:56
|
| no its still little vague. can you show some sample with the 5 date columns and some sample values and expected output.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
blackX
Posting Yak Master
102 Posts |
Posted - 2007-09-20 : 20:30:28
|
| I am at home now but I will try to explain better what I need. I want use the getdate() function on the first available column (first field that has either 1-1-1900 or null). If all five columns are the filled with valid dates then I would need to overwrite what is in letter5date. Any help would be great. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-20 : 20:41:57
|
| [code]UPDATE USET letter5date = CASE WHEN NullIf(letter4date, '19000101') IS NOT NULL THEN Getdate() ELSE letter5date END, letter4date = CASE WHEN NullIf(letter4date, '19000101') IS NULL AND NullIf(letter3date, '19000101') IS NOT NULL THEN Getdate() ELSE letter4date END, ... letter1date = CASE WHEN NullIf(letter1date, '19000101') IS NULL THEN Getdate() ELSE letter1date ENDFROM MyTable AS UWHERE ...[/code]Don't use 1-1-1900 as "never sent a mail", NULL is just fine for that, and much better. If you must have a Sent/NotSent type flag then add an additional BIT column, don't use a bogus date.Instead of 5 columns you should have a separate table for this information. Read up on "normalisation" if you are not familiar with the term.Kristen |
|
|
|
blackX
Posting Yak Master
102 Posts |
Posted - 2007-09-20 : 20:54:35
|
quote:
Originally posted by Kristen
UPDATE USET letter5date = CASE WHEN NullIf(letter4date, '19000101') IS NOT NULL THEN Getdate() ELSE letter5date END, letter4date = CASE WHEN NullIf(letter4date, '19000101') IS NULL AND NullIf(letter3date, '19000101') IS NOT NULL THEN Getdate() ELSE letter4date END, ... letter1date = CASE WHEN NullIf(letter1date, '19000101') IS NULL THEN Getdate() ELSE letter1date ENDFROM MyTable AS UWHERE ... Don't use 1-1-1900 as "never sent a mail", NULL is just fine for that, and much better. If you must have a Sent/NotSent type flag then add an additional BIT column, don't use a bogus date.Instead of 5 columns you should have a separate table for this information. Read up on "normalisation" if you are not familiar with the term.Kristen
The 1-1-1900 is not technically a bogus date. In our database program when a record is updated and the date field are blank it puts 1-1-1900 in the date fields. I am very familiar with normalisation, however my company is not. I started there in the beginning of June, and let me tell you how every programmed their database should be shot. Our main table is the "member" table which has 160 columns in it. I have been telling them that this is killing the performance but they seem content with it. So thanks I will try it out tomarrow and see if it works. One more thing, would your suggestion work as a stored procedure? Thanks for your help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-20 : 21:14:28
|
"In our database program when a record is updated and the date field are blank it puts 1-1-1900 in the date fields"If it were me I'd have a Trigger on that table take it back out again. Or have a default for 01-01-1900 and set it to NOT NULL. I think worse than the "bogus value", as I call it!, is having two possible values for "unused"."I am very familiar with normalisation, however my company is not."I wish I had $1 for every company I've been to like that!"Our main table is the "member" table which has 160 columns in it"I recommend SELECT * whenever you use that table, especially if it has lots of TEXT columns  "One more thing, would your suggestion work as a stored procedure?"Well, yes ... but I'm not sure why you think it wouldn't, so it feels like I'm answering the wrong question.Kristen |
|
|
|
blackX
Posting Yak Master
102 Posts |
Posted - 2007-09-21 : 11:39:01
|
| thank you very much, it works just as I needed it to. |
|
|
|
|
Help Please