tSQL Query to identify change in value

Posted on 2011-10-28
Last Modified: 2012-05-12
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
Question by:d1cjm1ex
    LVL 92

    Accepted Solution

    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

    LVL 11

    Expert Comment

    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

      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


    Execute this command and see the results:

    update your_table set Rent = 110 where  Month = 3
    LVL 59

    Expert Comment

    by:Kevin Cross
    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
    ) 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 (
    ) AS your_table(Tenant, [Month], Rent)

    Open in new window

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

    Best regards,


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now