[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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
0
d1cjm1ex
Asked:
d1cjm1ex
1 Solution
 
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now