tSQL Query to identify change in value

I need help with some tSQL code (sql server 2008) that will loop thru a table and flag(true or false) when a value has changed.  An example would be a table that contains monthly rent values by tenant.  when the rent changes from previous month I just want to set a flag on that months record to indicate a change in rent.  Example of result set is below:

Tenant      Month    Rent      Change
123              Jan            100      0
123              Feb            100      0
123              Mar            100      0
432              Jan            125      0
432              Feb            125      0
432              Mar            140      1
432              Apr            140      0
d1cjm1exAsked:
Who is Participating?
 
Patrick MatthewsCommented:
This is working for me.

I urge you NOT to put the change flag in the table, as it is a calculated column.  Instead, calculate it on demand in a query.


CREATE TABLE SomeTable(Tenant int, EffectiveDt datetime, Rent numeric(8, 2))

INSERT INTO SomeTable (Tenant, EffectiveDt, Rent)
SELECT 123, '2011-01-01', 100 UNION ALL
SELECT 123, '2011-02-01', 100 UNION ALL
SELECT 123, '2011-03-01', 100 UNION ALL
SELECT 432, '2011-01-01', 125 UNION ALL
SELECT 432, '2011-02-01', 125 UNION ALL
SELECT 432, '2011-03-01', 140 UNION ALL
SELECT 432, '2011-04-01', 140

SELECT t1.Tenant, t1.EffectiveDt, t1.Rent, CASE 
    WHEN (SELECT TOP 1 t2.Rent
        FROM SomeTable t2
        WHERE t2.EffectiveDt < t1.EffectiveDt AND t2.Tenant = t1.Tenant
        ORDER BY t2.EffectiveDt DESC) IS NULL THEN 0
    WHEN (SELECT TOP 1 t2.Rent
        FROM SomeTable t2
        WHERE t2.EffectiveDt < t1.EffectiveDt AND t2.Tenant = t1.Tenant
        ORDER BY t2.EffectiveDt DESC) <> t1.Rent THEN 1
    ELSE 0 END AS Changed
FROM SomeTable t1
ORDER BY t1.Tenant, t1.EffectiveDt

DROP TABLE SomeTable

Open in new window

0
 
dougaugCommented:
See if this works for you. I've created a table with Month as int to keep the query more simple.

create table your_table
(Tenant int,
 Month  int,
 Rent  int,
 Change int)

insert into your_table select 123, 1, 100, 0
insert into your_table select 123, 2, 100, 0
insert into your_table select 123, 3, 100, 0

insert into your_table select 432, 1, 100, 0
insert into your_table select 432, 2, 100, 0
insert into your_table select 432, 3, 140, 0
insert into your_table select 432, 4, 140, 0


create trigger yourupdtrig on your_table
for update
as
begin

  update your_table
     set your_table.Change = case when your_table.Rent <> (select a.Rent
                                                               from your_table a
                                                              where a.Tenant = your_table.Tenant
                                                                and a.Month = your_table.Month - 1) then 1 else 0 end
    from inserted
   where your_table.Tenant = inserted.Tenant
     and your_table.Month = inserted.Month

end

Execute this command and see the results:

update your_table set Rent = 110 where  Month = 3
0
 
Kevin CrossChief Technology OfficerCommented:
Wow, I am slow. I almost posted and noticed that two others already gave you options that appear to work just fine. I am with Patrick, though, I would NOT have this as a permanent column in your table and my solution was based on this fact also. For what it is worth, here is another option, i.e., what I was going to post originally.

=====
You have a number of options in SQL 2008. One that fits the simple case shown is:

;WITH cte(Tenant, [Month], Rent, RN) AS (

-- establish sequential row number in case sorting is complicated
-- unnecessary if your table has sequential values by row
SELECT Tenant, [Month], Rent
     , ROW_NUMBER() OVER(PARTITION BY Tenant ORDER BY [Month])
FROM (

-- sample values
-- this section is not needed in final solution
VALUES(123,1,100),
      (123,2,100),
      (123,3,100),
      (432,1,125),
      (432,2,125),
      (432,3,140),
      (432,4,140)

) AS your_table(Tenant, [Month], Rent)

)
-- final select
SELECT c.Tenant
     , c.[Month]
     , c.Rent
	 , CASE WHEN p.Rent <> c.Rent THEN 1 ELSE 0 END AS [Change]
FROM cte c
LEFT OUTER JOIN cte p 
   ON p.Tenant = c.Tenant AND p.RN = c.RN - 1
;

Open in new window


Jan, Feb, Mar, etc. does not sort chronologically; therefore, I used integer values to show point.
If you have those values in actually data, you will have to modify the ORDER BY of the OVER() clause to be something like CASE [Month] WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2 ... END or something like:
SELECT Tenant
     , MONTH(CONVERT(DATE, [Month] + ' 01, 1900')) AS [Month]
     , Rent
FROM (

VALUES(123,'Jan',100),
      (123,'Feb',100),
      (123,'Mar',100),
      (432,'Jan',125),
      (432,'Feb',125),
      (432,'Mar',140),
      (432,'Apr',140)

) AS your_table(Tenant, [Month], Rent)
;

Open in new window


Either way, I hope that helps steer you in the right direction.

Best regards,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.