How to display multiple rows based row difference …

I have a table as follows:

EmpID   FromYM  ToYM    EmpYl      EmpX1 
-----    ------  -----  ------     ----------
1001    202101  202101  20210103   20210103
1001    202102  202103  20210103   20210103

I want to split this data based on above data.

If FromYm(Means yearmonth) and ToYM(Means yearmonth) difference is two then result as two rows:

Example result :

EmpID   FromYM  ToYM    EmpYl     DiffNoCount
------  ------  ----    -----     ------
1001    202101  202101  20210103  1 
1001    202102  202103  20210103  1 
1001    202102  202103  20210103  2

Tried Code

IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
DROP TABLE #rawdata1
END
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
DROP TABLE #rawdata2
END


go

declare @Max as int
declare @Kount as int
Set @Kount = 1
SELECT row_number() Over (order by EmpID) as row,EmpID
,FromYM
,ToYM
, EmpYl
,EmpX1
into #rawdata1
FROM [dbo].[ASAAValue1]
order by EmpID
set @Max = (Select Max(FromYM) from #rawdata1)

Create Table #Rawdata2
(
[Row] int,
Rolling int,
RollingAvg decimal(15,2),
RollingFinal int
)
while (@kount < @max)
Begin
Insert into #rawdata2
select @Kount as Row , FromYM as Rolling

, ToYM as RollingAvg,

   Case When  Convert(int,Convert(nvarchar(6),EmpYl))>=FromYM
    Then  <br>
     FromYM   <br>
    Else<br>
      FromYM+1  <br>
     End <br>
 
from #rawdata1<br>
where row between @Kount - 12  and @Kount  <br>
set @Kount = @Kount + 1
end
select rd1.row,
rd1.EmpID,Rd1.FromYM,Rd1.ToYM,Rd1.EmpYl, rd2.RollingFinal as Final from #rawdata2 rd2
inner join #rawdata1 rd1
on rd1.row = rd2.row

Answer

You can do it using recursive cte:

with data as (
  select '202101' as f, '202101' as t union all
  select '202102' as f, '202103' as t
),
rcte as (
  select *, 1 as n from data
  union all
  select f, t, n + 1
  from rcte
  where n <= datediff(month, convert(date, f + '01', 112), convert(date, t + '01', 112))
)
select * from rcte

You can test on this db<>fiddle

How to display multiple rows based row difference data In SQL?

Advertisement

Answer