Need group by data based on when the data changes

1

Attached is the source data and how I want in target table through SQL query

image
image2016×1512 1.72 MB

2

You can use the LAG function in T-SQL:

LAG (Transact-SQL) - SQL Server | Microsoft Learn

As you did provide us a picture I cannot test it against your sample data, so it could be something like this. If you provide some sql with sample data I can test it and provide a tested example.

;WITH SomethingIDontKnow AS
(
SELECT mtr_id, msr, LAG(msr, 1,0) OVER PARTITION BY (mtr_id ORDER BY reading_date) AS prev_msr, reading_date
CASE WHEN msr <> LAG(msr, 1,0) OVER PARTITION BY (mtr_id ORDER BY reading_date) THEN 1 ELSE 0 AS [Group]
FROM Your_Table
) SELECT mtr_id, msr, reading_date, SUM([Group]) AS [Group]
FROM
SomethingIDontKnow
GROUP BY mtr_id, msr, reading_date;

3

Thanks for responding but it didn't work

image
image2016×1512 1.58 MB

4

Please provide some sample data so I can make it work :wink: You should get the idea I'm thinking about as only the sum isn't working.

5

Maybe it's not the best solution, but I'm not able to do better at the moment.

if object_id('tempdb..#t') is not null drop table #t
--
create table #t
(
	mtr_id int
	, msr int
	, reading_date date
)
insert into #t(mtr_id, msr, reading_date) values
	(5, 60, '20230508')
	, (5, 60, '20230509')
	, (5, 60, '20230510')
	, (5, 60, '20230511')
	, (5, 15, '20230512')
	, (5, 15, '20230513')
	, (5, 15, '20230514')
	, (5, 15, '20230515')
	, (5, 60, '20230516')
	, (5, 60, '20230517')
	, (5, 60, '20230518')
	, (5, 60, '20230519')
	, (5, 15, '20230520')
	, (5, 15, '20230521')
	, (5, 15, '20230522')
	, (5, 15, '20230523')
--
; with cte as
(
select 
	*
	, 
	(
		case 
			when lag(msr,1) over (partition by mtr_id order by reading_date) is null then 1
			when msr <> lag(msr,1) over (partition by mtr_id order by reading_date) then 1
		end	
	) as rn
	from #t
)
, cte2 as
(
	select 
		mtr_id
		, msr
		, reading_date
	from cte
	where rn=1
)
, cte3 as
(
	select
		* 
		, row_number() over (order by reading_date) as [Group]
	from cte2
)
select 
	cte.mtr_id, cte.msr, cte.reading_date
	, t.[Group]
from cte
outer apply
(
	select top 1 
		[Group]
	from 
		cte3 
	where 
		cte3.mtr_id=cte.mtr_id 
		and cte3.msr=cte.msr 
		and cte3.reading_date <= cte.reading_date 
	order by 
		cte3.reading_date desc
) as t
order by 
	cte.reading_date
1 Like
6

This is working fine as of now on Dev data. Let see how it behaves in prod. Thank you very much for your help. Will update you of prod

1 Like