T-sql Reset Row number on Field Change
Similar to a recent post of mine "t-sql sequential duration"”, but not exactly the same, I want to reset the row number based on a change in column x (in my case, column "who").
Here's the first query that returns the a small sample of the raw(ish) data:
SELECT DISTINCT chr.custno,
CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
chr.who
FROM <TABLE> chr
WHERE chr.custno = 581827
AND LEFT(chr.who, 5) = 'EMSZC'
AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
ORDER BY chr.custno
Result:
custno moddate who
581827 2012-11-08 08:38:00.000 EMSZC14
581827 2012-11-08 08:41:10.000 EMSZC14
581827 2012-11-08 08:53:46.000 EMSZC14
581827 2012-11-08 08:57:04.000 EMSZC14
581827 2012-11-08 08:58:35.000 EMSZC14
581827 2012-11-08 08:59:13.000 EMSZC14
581827 2012-11-08 09:00:06.000 EMSZC14
581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1
581827 2012-11-08 09:05:04.000 EMSZC49
581827 2012-11-08 09:06:32.000 EMSZC49
581827 2012-11-08 09:12:03.000 EMSZC49
581827 2012-11-08 09:12:38.000 EMSZC49
581827 2012-11-08 09:14:18.000 EMSZC49
581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
Second step is to add the row number (I didn’t do this in the first query because of the use of the word DISTINCT); so…
WITH c1 AS (
SELECT DISTINCT chr.custno
CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
chr.who
FROM <TABLE> chr
WHERE chr.custno = 581827
AND LEFT(chr.who, 5) = 'EMSZC'
AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
)
SELECT ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
FROM c1
Result:
RowID custno moddate who
1 581827 2012-11-08 08:38:00.000 EMSZC14
2 581827 2012-11-08 08:41:10.000 EMSZC14
3 581827 2012-11-08 08:53:46.000 EMSZC14
4 581827 2012-11-08 08:57:04.000 EMSZC14
5 581827 2012-11-08 08:58:35.000 EMSZC14
6 581827 2012-11-08 08:59:13.000 EMSZC14
7 581827 2012-11-08 09:00:06.000 EMSZC14
8 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1
9 581827 2012-11-08 09:05:04.000 EMSZC49
10 581827 2012-11-08 09:06:32.000 EMSZC49
11 581827 2012-11-08 09:12:03.000 EMSZC49
12 581827 2012-11-08 09:12:38.000 EMSZC49
13 581827 2012-11-08 09:14:18.000 EMSZC49
14 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
The next step is where I’m stuck: the goal is to reset the RowID to 1 on each change of value in the “who” column. The following code gets an “almost there” result (and it should be noted that I stole/borrowed this code from somewhere, but now I can’t find the website):
WITH c1 AS (
SELECT DISTINCT chr.custno,
CAST(LEFT(CONVERT( VARCHAR(20),chr.moddate,112),10)+ ' ' + chr.modtime AS DATETIME)as moddate,
chr.who
FROM <TABLE> chr
WHERE chr.custno = 581827
AND LEFT(chr.who, 5) = 'EMSZC'
AND chr.[description] NOT LIKE 'Recalled and viewed this customer'
)
, c1a AS (
SELECT ROW_NUMBER() OVER (PARTITION BY custno ORDER BY custno, moddate, who) AS RowID, custno, moddate, who
FROM c1
)
SELECT x.RowID - y.MinID + 1 AS Row,
x.custno, x.Touch, x.moddate, x.who
FROM (
SELECT custno, who, MIN(RowID) AS MinID
FROM c1a
GROUP BY custno, who
) AS y
INNER JOIN c1a x ON x.custno = y.custno AND x.who = y.who
Result:
Row custno moddate who
1 581827 2012-11-08 08:38:00.000 EMSZC14
2 581827 2012-11-08 08:41:10.000 EMSZC14
3 581827 2012-11-08 08:53:46.000 EMSZC14
4 581827 2012-11-08 08:57:04.000 EMSZC14
5 581827 2012-11-08 08:58:35.000 EMSZC14
6 581827 2012-11-08 08:59:13.000 EMSZC14
7 581827 2012-11-08 09:00:06.000 EMSZC14
1 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1 (Hooray! It worked!)
2 581827 2012-11-08 09:05:04.000 EMSZC49
3 581827 2012-11-08 09:06:32.000 EMSZC49
4 581827 2012-11-08 09:12:03.000 EMSZC49
5 581827 2012-11-08 09:12:38.000 EMSZC49
6 581827 2012-11-08 09:14:18.000 EMSZC49
14 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1 (Crappies.)
DESIRED result:
Row custno moddate who
1 581827 2012-11-08 08:38:00.000 EMSZC14
2 581827 2012-11-08 08:41:10.000 EMSZC14
3 581827 2012-11-08 08:53:46.000 EMSZC14
4 581827 2012-11-08 08:57:04.000 EMSZC14
5 581827 2012-11-08 08:58:35.000 EMSZC14
6 581827 2012-11-08 08:59:13.000 EMSZC14
7 581827 2012-11-08 09:00:06.000 EMSZC14
1 581827 2012-11-08 09:04:39.000 EMSZC49 Reset row number to 1
2 581827 2012-11-08 09:05:04.000 EMSZC49
3 581827 2012-11-08 09:06:32.000 EMSZC49
4 581827 2012-11-08 09:12:03.000 EMSZC49
5 581827 2012-11-08 09:12:38.000 EMSZC49
6 581827 2012-11-08 09:14:18.000 EMSZC49
1 581827 2012-11-08 09:17:35.000 EMSZC14 Reset row number to 1
Any assistance is appreciated.
Answer
If you are on SQL Server 2012 you can use LAG to compare value with previous row and you can use SUM and OVER to record the changes.
with C1 as
(
select custno,
moddate,
who,
lag(who) over(order by moddate) as lag_who
from chr
),
C2 as
(
select custno,
moddate,
who,
sum(case when who = lag_who then 0 else 1 end)
over(order by moddate rows unbounded preceding) as change
from C1
)
select row_number() over(partition by change order by moddate) as RowID,
custno,
moddate,
who
from C2
Update:
A version for SQL Server 2005. It uses a recursive CTE and a temp table for intermediary storage of the data you need to iterate over.
create table #tmp
(
id int primary key,
custno int not null,
moddate datetime not null,
who varchar(10) not null
);
insert into #tmp(id, custno, moddate, who)
select row_number() over(order by moddate),
custno,
moddate,
who
from chr;
with C as
(
select 1 as rowid,
T.id,
T.custno,
T.moddate,
T.who,
cast(null as varchar(10)) as lag_who
from #tmp as T
where T.id = 1
union all
select case when T.who = C.who then C.rowid + 1 else 1 end,
T.id,
T.custno,
T.moddate,
T.who,
C.who
from #tmp as T
inner join C
on T.id = C.id + 1
)
select rowid,
custno,
moddate,
who
from C
option (maxrecursion 0);
drop table #tmp;