CODEWITHSUNDEEP
sqlsql-servert-sql
BIReportGuy
BIReportGuy

Reputation: 817

TSQL - Select rows where column value has changed

I have a Rep table with the following fields:

enter image description here

There can be changes to the RepNbr. I need to write a query that shows what the repnbr was for each effectivedate. What the repnbr was prior and then when there's a change. So, I'm trying to pull the repnbr of M258 on 10/21 (what the repnbr was prior), the repnbr of DM25 on 6/3/16 and what the repnbr of M258 was on 6/8/16.

I tried the following query: This query only pulls the effectivedate of 6/3 and 6/8 and doesn't look back to what the repnbr was previously.

    ;with t1 as
(
select 
     acctnbr, 
     repnbr, 
     effectivedate,
     rn = row_number() over (partition by repnbr order by acctnbr)
from 
     reptable
where 
     acctnbr = '123'
)
select 
     *
from 
     t1
where  
     rn = '1'
order by 
     effectivedate

Results:

enter image description here

My output should look like:

enter image description here

What am I doing wrong with my query? Thanks,

Upvotes: 0

Views: 1076

Answers (2)

Matt
Matt

Reputation: 14341

USE LAG() and or LEAD() functions for SQL 2012+

DECLARE @Rep AS TABLE (Acct INt, RepNbr CHAR(4), EffectiveDate DATETIME)
INSERT INTO @Rep VALUES
(123,'M258','2015-10-15')
,(123,'M258','2015-10-21')
,(123,'DM25','2015-06-03')
,(123,'M258','2015-06-08')

;WITH cte AS (
    SELECT *
       ,LAG(RepNbr) OVER (PARTITION BY Acct ORDER BY EffectiveDate) as PrevRepNbr
    FROM
       @Rep
)

SELECT Acct, RepNbr, EffectiveDate
FROM
    cte 
WHERE
    PrevRepNbr IS NULL
    OR PrevRepNbr <> RepNbr

Note though that your example isn't in order of Effective Date so your results will not be as you described. Because 10-21 is after 6-3...

Acct    RepNbr  EffectiveDate
123     DM25    2015-06-03 00:00:00.000
123     M258    2015-06-08 00:00:00.000
123     M258    2015-10-15 00:00:00.000
123     M258    2015-10-21 00:00:00.000

So that would mean that the RepNbr only changes on 6/8 from DM25 to M258

If you change your example table to:

DECLARE @Rep AS TABLE (Acct INt, RepNbr CHAR(4), EffectiveDate DATETIME)
INSERT INTO @Rep VALUES
(123,'M258','2015-06-03')
,(123,'M258','2015-06-08')
,(123,'DM25','2015-10-15')
,(123,'M258','2015-10-21')

Then it woudl start with M258 on 6/3 change to DM25 on 10/15 and then change back to M258 on 10/21.

If you don't want to see the original/starting value just remove the WHERE PrevRepNbr IS NULL

Edit without @Rep:

;WITH cte AS (
    SELECT
       Acct
       ,RepNbr
       ,EffectiveDate
       ,LAG(RepNbr) OVER (PARTITION BY Acct ORDER BY EffectiveDate) as PrevRepNbr
    FROM
       TableName
)

SELECT Acct, RepNbr, EffectiveDate
FROM
    cte 
WHERE
    PrevRepNbr IS NULL
    OR PrevRepNbr <> RepNbr

Upvotes: 2

Serg
Serg

Reputation: 22811

Older Sql sever versions grouping

DECLARE @Rep AS TABLE (Acctnbr int, RepNbr CHAR(4), EffectiveDate DATETIME)
INSERT INTO @Rep VALUES
(123,'M258','2015-10-15')
,(123,'M258','2015-10-21')
,(123,'DM25','2015-06-08')
,(123,'M258','2015-06-03');

WITH cte AS (
  select *, 
   grp = row_number() over(partition by acctnbr order by effectivedate)
   - row_number() over(partition  by acctnbr, RepNbr  order by effectivedate)
     from @rep
)
SELECT acctnbr, RepNbr, max(effectivedate) effectivedate
from cte
group by acctnbr, RepNbr, grp
order by acctnbr, max(effectivedate) desc;

Upvotes: 2

Related Questions