Attached is the source data and how I want in target table through SQL query
You can use the LAG function in T-SQL:
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]
) SELECT mtr_id, msr, reading_date, SUM([Group]) AS [Group]
GROUP BY mtr_id, msr, reading_date;
Please provide some sample data so I can make it work You should get the idea I'm thinking about as only the sum isn't working.
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
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