| Author |
Topic |
|
MrNobody
Starting Member
5 Posts |
 Posted - 2007-03-14 : 14:51:01
|
| Hello,I just joined the forum and consider myself a SQL beginner. I have installed and supported, run backups and similar, but haven't done much with queries and scripting.I have a MS SQL 2000 server with a company database on it. Amongst other things, the database contains names, birthdays and e-mail addresses. How difficult would it be to setup an automated query to run every morning comparing birthdays to the current date and send automated e-mails out to matching people saying "Happy birthday ...."?Thanks in advance,MrNobody"If I'm not there, I must be somewhere else" |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-15 : 06:41:01
|
| 1. automated query....investigate SQL Agent2. comparing birthdays to the current date...small SQL query involving = and/or the dateadd function.3. email....search here for 'solutions' already posted on this topic....think there is one posted under the FAQ/Article sections.4. BOL will be a good friend to you as you progress....as may we (IF you are seen to be helping yourself) |
 |
|
|
MrNobody
Starting Member
5 Posts |
Posted - 2007-03-15 : 14:44:06
|
| Thank you for your response. It gave me the most important answer which is yes, it is possible to do what I'm looking to do. After delving deeper, I've found that the query and sql agent parts won't be too difficult. I've almost got the query doing exacly what I want. I just need to figure out the best way to remove duplication of clients who have done business with us multiple times. I didn't have much success with DISTINCT so I'm looking at grouping. I'll finish the query up and then start digging deeper into the email side of things. MrNobody :)"If I'm not there, I must be somewhere else" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-16 : 06:48:17
|
| With distinct only look at the fields which guarantee uniqueness, and then if you need to join back to get some non-unique info, you may have to choose the min/max of the non-unique info...murphy, andrew, emailaddress1murphy, andrew, emailaddress2select distinct surname, firstname from mytableorslect surname, firstname, min(email) from mytable group by surname, firstname |
|
|
|
MrNobody
Starting Member
5 Posts |
Posted - 2007-03-16 : 07:28:08
|
| It seems the problem I was running into with Distinct is that all the data isn't in one table, so I am doing some joins. I would like to use the Social Sec number as the field I want to filter by. The Soc isn't stored in the same table as the name or email address, so I've joined them, linked by a common field for the transaction ID. Distinct works fine when I am just doing one table but stops working when I'm joining tables. When I get to the office, I wil try min.Thanks"If I'm not there, I must be somewhere else" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-16 : 08:52:43
|
| distinct shouldn't have an issue with joins.post some smaple DDL and sample input data and we'll try to work something out. |
|
|
|
MrNobody
Starting Member
5 Posts |
Posted - 2007-03-16 : 09:32:53
|
| I'm not 100% sure I'm going to give this in the right format, but ....Here is the basic code I'm trying to use:SELECT DISTINCT Extra.f108 AS BorSoc, Files.iFileID, Files.f19 AS LO, Files.f100 AS BorFirst, Files.f101 AS BorLast, Files.f118 AS BorBD, Extra.f112 AS BorEmailFROM Extra INNER JOIN Files ON Extra.iFileID = Files.iFileID WHERE (LEFT(Files.f118, 5) = LEFT(CONVERT(varchar, GETDATE(), 101), 5))Extra:iFileID|f108 |f112 10001 |123-45-1000|joe@test.com10002 |123-45-2000|bob@test.com10003 |123-45-2000|bob@test.com10004 |123-45-3000|Tom@test.com10005 |123-45-4000|sam@test.com10006 |123-45-1000|joe@test.comFiles:iFileID|F19 |f100|f101 |f11810001 |John|Joe |Black|03/16/196910002 |Mike|Bob |Smith|03/15/197610003 |Mike|Bob |Smith|03/15/197610004 |Dan |Tom |Jones|03/16/197610005 |Ron |Sam |Raime|03/15/197610006 |John|Joe |Black|03/16/1969(f19 is the rep in the company who worked with them)In this example, I wouldn't want to see Bob Smith and Joe Black on the results twice. With my data, the distinct seems to make no difference. Thanks"If I'm not there, I must be somewhere else" |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-16 : 12:26:44
|
| why aren't you just doing....??...since the requirement is to just get a list of email addresses?SELECT DISTINCT Extra.f112 AS BorEmailFROM Extra INNER JOIN Files ON Extra.iFileID = Files.iFileID WHERE (LEFT(Files.f118, 5) = LEFT(CONVERT(varchar, GETDATE(), 101), 5))also....be aware of date conversion issues, especially from text values...ie 12/1 the 1st dec or 12th jan? best format is ccyymmdd. |
|
|
|
MrNobody
Starting Member
5 Posts |
Posted - 2007-03-16 : 12:48:39
|
| The 1st goal is to get a list of emails and send them a personalized e-mail saying "Happy Birthday" 2nd part will be to send an e-mail to the Sales Person who deals with that customer saying "It is [client's name]'s birthday today." so that we can call them in person. This email will need the name and contact info as well as other information I'll worry about later. Lastly it has been requested that the manager also get a list of all birthdays each day including the Borrower name, the Contact working with them and the contact info so the manager can have people call on deals where the primary contact at the company no longer works here ...Fun fun!"If I'm not there, I must be somewhere else" |
|
|
|
|
Generating automated emails from birthdays