I'm trying to update the SMS table with Emp_Name & Emp_ID where SMS_Number matches Emp_CellNumber where processed is false.
The (non working) code I have at the moment is:
BEGIN
DECLARE @Emp_ID INT
DECLARE @Emp_Name NVARCHAR(50)
IF EXISTS (SELECT 1 @Emp_ID = Emp_ID, (Emp_FName + ' ' + Emp_SName) As @Emp_Name = Emp_Name
FROM Employees
WHERE Status = 'Live'
AND REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')
BEGIN
INSERT INTO SMS (Emp_ID, Emp_Name)
VALUES (@Emp_ID, @Emp_Name)
WHERE Processed = 'False'
END
END
Any pointers as to where I've gone wrong or a better way to achieve this please?
First off: If [SMS] and [Employees] have some columns in common (e.g. name and CellNumber) then GET RID of those columns from one table (e.g. keep the Name in [Employees] and the [CellNumber] in [SMS]. Otherwise whenever they become different, for whatever reason, you won't know which version to trust.
(That might not be an issue for you, but just wanted to point it out. For more info Google: "First Normal Form")
I see that you are concatenating First and Last name to make the Name in SMS. If you do choose to remove the Name from [SMS] you can use a VIEW for the concatenated name, for example something like this:
CREATE VIEW SMS_WIthName
AS
SELECT S.SMS_ID, S.SMS_Number, S.SMS_Message, S.Emp_ID, S.Processed,
(E.Emp_FName + ' ' + E.Emp_SName) AS Emp_Name
FROM SMS AS S
JOIN Employees AS E
ON E.Emp_ID = S.Emp_ID
If you want to insert new rows into [SMS] for rows in [Employees] table then something like this:
INSERT INTO SMS (Emp_ID, Emp_Name)
SELECT Emp_ID, (Emp_FName + ' ' + Emp_SName)
FROM Employees
WHERE Status = 'Live'
AND REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')
REPLACE(Emp_CellNumber, ' ', '') LIKE '%123456789')
this means "Any cell number ending in "123456789" (ignoring any spaces). Just checking that is what you want?
Correct I was concatenating Emp_FName & Emp_SName as the name doesn't need to be seperate in this table.
Correct I was removing all spaces and ignoring the first characters as its either '0' or '+44' depending how its been added.
I think I may have badly explained what I'm attempting to do. Employees has lots of names & phone numbers in it. SMS contains raw incoming text messages.
Basically I'm trying to cross reference the incoming text message number (SMS_Number) with the phone numbers contained in Employees (Emp_CellNumber). If there is a match copy the name & EMP_ID from Employee to SMS and chnage SMS_Processed to True. ie
SMS Table
SMS_ID SMS_Number SMS_Message SMS_Emp_Name SMS_Emp_ID SMS_Processed.
1 +441111111111 message 1 NULL NULL False
2 0222222222 message 2 NULL NULL False
3 +44333333333 message 3 NULL NULL False
4 09999999999 message 4 NULL NULL False
Employees Table:
Emp_ID Emp_FName Emp_SName Emp_CellNumber
1000 Bob Smith 01111 111111
1001 Jane Doe 02222 222222
1002 John Brown 03333 333333
Result SMS Table:
SMS_ID SMS_Number SMS_Message SMS_Emp_Name SMS_Emp_ID SMS_Processed.
1 441111111111 message 1 Bob Smith 1000 True
2 0222222222 message 2 Jane Dow 1001 True
3 +44333333333 message 3 John Brown 1002 True
4 09999999999 message 4 NULL NULL True
I wasn't really meaning that, although personally I prefer that sort of naming convention.
I would not have the NAME in both Employees and SMS table - you can get the name, whenever you want, by JOINing the SMS table to the Employees table.
I can see how that works in a transaction processing sense, and perhaps your SMS_Processed column takes care of it, but the risk exists for your update to assign an Employee-ID to the SMS record and then the CellNumber on the Employee record changes (they buy a new phone) or someone changes the SMS record (to manually correct the Employee ID or SMS_Number. Now the SMS record and the Employee record contain the same data columns in both tables (Employee Name and Cell Number) but they contain conflicting data. Looking at it you won't know which of the two possibilities is the correct one.
I certainly would not put the Employee's Name in the SMS table - that exists in the Employee table. If they get married / whatever and their name changes are you going to update all the historical SMS records? Just store that information in one place.
For me, ideally, if you put the Employee ID in the SMS record (having matched on SMS Number / EMP CellNumber) then you might want to change the SMS Number to NULL - i.e. if there is an Employee ID in the SMS record then there is no longer any need to hold the SMS Number (there may be a need, to know what the SMS Number was AT THE TIME the record was made, but if not don't store it in duplicate, just keep it in one place).
Another approach would be to have a LINK table that joins EMP to SMS based on the Cell Number. The link table would be EMP_ID and SMS_Number. You can then join that to Employees table (to get the name) and to the SMS table (to get the Message etc.)
Why update the SMS table at all? Is there some reason not to just JOIN the two tables at the time that you produce the report?
Thanks fot the reply. I've just read it a few times and you are quite right, you've made a couple of very valid points that I overlooked... It serves no purpose to keep the name in the SMS table!!
I'm wondering if I can simplify the entire process. Is it possible to do the phone number comparison in the insert statement?
Hmmmm this compiles but doesn't seem to work....
DECLARE @SMS_ID nvarchar(20) = 8888
DECLARE @SMS_Number nvarchar(15) = 441234567890
DECLARE @SMS_Message nvarchar(160) = 'text message blar blar'
DECLARE @SMS_Processed bit = 'False'
BEGIN
IF NOT EXISTS (SELECT SMS_ID FROM SMS
WHERE SMS_ID = @SMS_ID)
BEGIN
INSERT INTO SMS (SMS_ID, SMS_Number, SMS_Message, SMS_Processed, SMS_Emp_ID)
VALUES (@SMS_ID, @SMS_Number, @SMS_Message, @SMS_Processed, (
SELECT Emp_ID
FROM Employees
WHERE Emp_Status = 'Live'
AND (REPLACE(Emp_CellNumber, ' ', '')) LIKE (REPLACE(@SMS_Number,left(@SMS_Number,2),''))
))
END
END