Hi
Input test data:
create table #T
(
TestType int not null,
TestDate date not null primary key clustered,
constraint T_CK check(TestType in (7, 8, 9))
)
insert #T(TestType, TestDate)
select 7, '2019-07-10' union all
select 7, '2019-11-14' union all
select 8, '2019-11-26' union all
select 9, '2019-11-30' union all
select 9, '2020-01-30' union all
select 8, '2020-05-14' union all
select 7, '2020-09-16'
select TestType, TestDate
from #T
order by TestDate
drop table #T
And for the output Result column to be as below. Tried playing around with window ranking functions but missing something.
TestType TestDate Result
7 2019-07-10 1
7 2019-11-14 1
8 2019-11-26 2
9 2019-11-30 3
9 2020-01-30 3
8 2020-05-14 4
7 2020-09-16 5
with cte as (
select TestType, TestDate, case when lag(TestType) over(order by TestDate) = TestType then 0 else 1 end as IsStart
from #T
)
select TestType, TestDate,sum(IsStart) over(order by TestDate) as Result
from cte
order by TestDate;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537October 5, 2021 at 1:49 pm
Almost identical to Mark"s solution
WITH groups
AS (SELECT t.TestType
,t.TestDate
,IsNew = IIF(ISNULL(LAG(t.TestType, 1) OVER (ORDER BY t.TestDate), -1) <> t.TestType, 1, 0)
FROM #T t)
SELECT g.TestType
,g.TestDate
,GroupNo = SUM(g.IsNew) OVER (ORDER BY g.TestDate ROWS UNBOUNDED PRECEDING)
FROM groups g
ORDER BY g.TestDate;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 5, 2021 at 3:03 pm
Many thanks to both of you!
I'd got bogged down in trying to use row_number and dense_rank, and didn't think about using lag.
October 10, 2021 at 11:54 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply