Comparing two rows from a table, reporting differences 1

[alicorn2]

I have a table called Position that has a structure similar to the following:

PositionID - unique key
PersonnelID - foreign key
Company
Start Date
End Date
Address1
City
etc etc etc

When a new row is inserted, I want to compare it to the previous row for the same personnel ID, and then send an email listing only what has changed. This is all working now, but it seems very clunky the way I've done it, and I would like to know if there is a way to streamline it.

What I've done is declared a variable for every field in the table... twice. E.g. I have @newStartDate and @oldStartDate. Then I select values into each of those variables using something similar to
select @newStartDate = StartDate, @newEndDate = EndDate, [etc] where PositionID = @NewID

Then I go through a huge block of
if @newStartDate <> @oldStartDate
select @msg = @msg + 'Start Date changed from' + @oldStartDate + ' to ' + @newStartDate

(My code has all the proper casts and checks for nulls, I'm just simplifying)

At the end of everything, if @msg isn't blank, I send an email to the appropriate people.

ANYWAY... is there a way in T-SQL (using Microsoft SQL Server 8 currently, about to transition over to SQL 2005 which is why I'm looking at this now) to do this more efficiently? I thought about having only two variables (@newVal and @oldVal) and doing a select for each pair of values, but decided that saved on variables, but was way less efficient!)

Thank you for any direction/suggestions you can provide!

 

[alicorn2]

No thoughts? Is the way I'm doing it the best/only way?

 

[alicorn2]

Here's the actual code, if it helps... This has been changed to run interactively in Query Analyzer, rather than running as a stored procedure and sending email at the end, rather than a select *... but you should get the idea!

declare @newPID int, @oldPID int
, @Val varchar(30), @oVal varchar(30)
select @newPID = 34, @oldPID = 33
select * from position where PositionID in (@newPid,@oldPID)

declare @tbl table  
(
	[Section] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Changed From] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Changed To] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
-- new record
declare
  @Company varchar (50)
, @StartDate smalldatetime 
, @EndDate smalldatetime 
, @Address1 varchar (50)  
, @Address2 varchar (50)  
, @City varchar (50)  
, @Province varchar (2)  
, @PostalCode varchar (7)  
, @AreaCode varchar (3)  
, @Phone varchar (7)  
, @FaxAreaCode varchar (3)  
, @Fax varchar (7)  
, @Comments varchar (1000)  
, @UpdateDate smalldatetime 
, @UpdateUser varchar (30)  
, @LocationID smallint 
, @LocationID_Mailing smallint 

-- old record
declare
  @oCompany varchar (50)
, @oStartDate smalldatetime 
, @oEndDate smalldatetime 
, @oAddress1 varchar (50)  
, @oAddress2 varchar (50)  
, @oCity varchar (50)  
, @oProvince varchar (2)  
, @oPostalCode varchar (7)  
, @oAreaCode varchar (3)  
, @oPhone varchar (7)  
, @oFaxAreaCode varchar (3)  
, @oFax varchar (7)  
, @oComments varchar (1000)  
, @oUpdateDate smalldatetime 
, @oUpdateUser varchar (30)  
, @oLocationID smallint 
, @oLocationID_Mailing smallint 

 Select  
   @Company = Company
 , @StartDate = StartDate
 , @EndDate = EndDate
 , @Address1 = Address1
 , @Address2 = Address2
 , @City = City
 , @Province = Province
 , @PostalCode = PostalCode
 , @AreaCode = AreaCode
 , @Phone = Phone
 , @FaxAreaCode = FaxAreaCode
 , @Fax = Fax
 , @Comments = Comments
 , @LocationID = LocationID
 , @LocationID_Mailing = LocationID_Mailing
 from Position 
 where PositionID = @newPID

 Select  
   @oCompany = Company
 , @oStartDate = StartDate
 , @oEndDate = EndDate
 , @oAddress1 = Address1
 , @oAddress2 = Address2
 , @oCity = City
 , @oProvince = Province
 , @oPostalCode = PostalCode
 , @oAreaCode = AreaCode
 , @oPhone = Phone
 , @oFaxAreaCode = FaxAreaCode
 , @oFax = Fax
 , @oComments = Comments
 , @oLocationID = LocationID
 , @oLocationID_Mailing = LocationID_Mailing
 from Position 
 where PositionID = @oldPID

if isnull(@Company, '') <> isnull(@oCompany, '') 
	insert @tbl values ('Company', @oCompany, @Company)

if isnull(@StartDate, '') <> isnull(@oStartDate, '') 
	insert @tbl values ('Start Date', cast(@oStartDate as varchar(11)), cast(@StartDate as varchar(11)))

-- etc -- repeat for every field in the table

select * from @tbl

 

[dbomrrsm]

Get the conversation about triggers rolling ......

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[gmmastros]

Look Ma... no variables!

Ok, there's one variable, but it doesn't hold data from your tables. It's only purpose is to hold the differences in your data.

I will admit that it is not perfect (based on your requirements). It may suit your needs though. To illustrate, I create a table variable to hold some dummy data, and then I show you a query that should generate the results you are looking for. Instead of showing the differences for each field in a seperate row, I am seperating the differences by Cr Lf in a string. I suspect that this will be sufficient for your purposes. When you run this in query analyzer, you won't see the CR LF because Query analyzer just doesn't do that.

[green]-- Dummy data[/green]
Declare @Temp Table(Id int, F1 varchar(20), F2 varchar(20))

Insert into @Temp Values(1, 'apple', 'red')
Insert into @Temp Values(2, 'Banana', 'Yellow')

[green]-- The code[/green]
Declare @Changes VarChar(8000)
Set @Changes = ''

Select @Changes = @Changes + 
       Case When A.F1 <> B.F1
            Then 'Field1: ' + A.F1 + ' changed to ' + B.F1 + Char(13) + Char(10)
            Else '' End
       + Case When A.F2 <> B.F2
              Then 'Field2: ' + A.F2 + ' changed to ' + B.F2 + Char(13) + Char(10)
              Else '' End
From    @Temp A
        Inner Join @Temp B
          On  A.Id = 1
          And B.Id = 2

Select @Changes

Also... this code will put an extra CR LF on the end of the changes string. If this causes you problems, I assume you'll know how to remove it. If not, let me know.

Try playing around with the insert into @Temp lines to verify that this query does what you want it to.

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Actually, you don't need any variable at all. Change the code section to this...

Select Case When A.F1 <> B.F1
            Then 'Field1: ' + A.F1 + ' changed to ' + B.F1 + Char(13) + Char(10)
            Else '' End
       + Case When A.F2 <> B.F2
              Then 'Field2: ' + A.F2 + ' changed to ' + B.F2 + Char(13) + Char(10)
              Else '' End
From   @Temp A
       Inner Join @Temp B
         On  A.Id = 1
         And B.Id = 2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[alicorn2]

Thanks, George! I do need a variable, since I need to send it off on its merry way in the world (i.e. to email) afterwards, but I didn't think of building it all in one big select/case statement. Thank you!