SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- table structure
CREATE TABLE [dbo].[ir_Costs](
    [EID] [varchar](9) COLLATE Latin1_General_CI_AS NOT NULL,
    [CLMinitialDT] [date] NOT NULL,
    [MonthYear] [date] NOT NULL,
    [Lodging] [smallmoney] NULL,
    [others] [smallmoney] NULL,
    [DaysPartial] [tinyint] NULL,
 CONSTRAINT [PK_ir_Costs] PRIMARY KEY CLUSTERED 
(
    [EID] ASC,
    [CLMinitialDT] ASC,
    [MonthYear] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

insert into ir_Costs(EID,CLMinitialDT,MonthYear,Lodging,Others,DaysPartial) values
 ('049','2018-07-03','2019-04-01',435.98,0.00,30)
,('049','2018-07-03','2019-05-01',435.98,0.00,30)
,('049','2018-07-03','2019-06-01',435.98,0.00,30)
,('049','2018-07-03','2019-07-01',130.79,0.00,9)
,('049','2018-07-03','2020-01-01',261.59,0.00,18)
,('049','2018-07-03','2020-02-01',435.98,0.00,28)
,('049','2018-07-03','2020-03-01',435.98,0.00,30)
,('049','2018-07-03','2020-04-01',435.98,0.00,30)
,('049','2018-07-03','2020-05-01',435.98,0.00,30)
,('049','2018-07-03','2020-06-01',435.98,0.00,30)
,('049','2018-07-03','2020-07-01',421.45,0.00,29)
,('049','2021-05-21','2022-08-01',53.60,0.00,4)
,('049','2021-05-21','2022-12-01',77.58,0.00,16)
,('049','2021-05-21','2023-01-01',415.46,0.00,30)
,('049','2021-05-21','2023-02-01',415.46,0.00,28)
,('049','2021-05-21','2023-03-01',415.46,0.00,30)
,('049','2021-05-21','2023-04-01',415.46,0.00,30)
,('049','2021-05-21','2023-05-01',415.46,0.00,30)
,('049','2021-05-21','2023-06-01',439.97,0.00,30)
,('049','2021-05-21','2023-07-01',263.98,0.00,18)
,('376','2019-08-01','2020-08-01',1500.00,100.00,30)
,('376','2019-08-01','2020-09-01',1500.00,0.00,30)
,('376','2019-08-01','2020-10-01',1500.00,0.00,30)
,('376','2019-08-01','2020-11-01',1500.00,0.00,30)
,('376','2019-08-01','2020-12-01',1500.00,100.00,30)
,('376','2019-08-01','2021-01-01',1500.00,0.00,30)
,('376','2019-08-01','2021-02-01',1500.00,100.00,28)
,('376','2019-08-01','2021-03-01',1500.00,0.00,30)
,('376','2019-08-01','2021-04-01',1500.00,100.00,30)
,('376','2019-08-01','2021-05-01',1500.00,0.00,30)
,('376','2019-08-01','2021-06-01',1500.00,100.00,30)
,('376','2019-08-01','2021-07-01',1500.00,100.00,30)
,('376','2019-08-01','2021-08-01',1500.00,100.00,30)
,('376','2019-08-01','2021-09-01',1500.00,100.00,30)
,('376','2019-08-01','2021-10-01',1500.00,100.00,30)
,('376','2019-08-01','2021-11-01',1500.00,100.00,30)
,('376','2019-08-01','2021-12-01',1500.00,0.00,30)
,('376','2019-08-01','2022-01-01',22.00,0.00,30)
,('376','2019-08-01','2022-02-01',1522.00,0.00,28)
,('376','2019-08-01','2022-03-01',1522.00,0.00,30)
,('376','2019-08-01','2022-04-01',1522.00,0.00,30)
,('376','2019-08-01','2022-05-01',1522.00,0.00,30)
,('376','2019-08-01','2022-06-01',1522.00,0.00,30)
,('376','2019-08-01','2022-07-01',1522.00,0.00,30)
,('376','2019-08-01','2022-08-01',1522.00,0.00,30)
,('376','2019-08-01','2022-09-01',1522.00,0.00,30)
,('376','2019-08-01','2022-10-01',1522.00,0.00,30)
,('376','2019-08-01','2022-11-01',1522.00,0.00,30)
,('376','2019-08-01','2022-12-01',1522.00,0.00,30)
,('376','2019-08-01','2023-01-01',1522.00,0.00,30)
,('376','2019-08-01','2023-02-01',1522.00,0.00,28)
,('376','2019-08-01','2023-03-01',1522.00,0.00,30)
,('376','2019-08-01','2023-04-01',1552.00,0.00,30)
,('376','2019-08-01','2023-05-01',1552.00,0.00,30)
,('376','2019-08-01','2023-06-01',1552.00,0.00,30)
,('376','2019-08-01','2023-07-01',1552.00,0.00,30)
,('376','2019-08-01','2023-08-01',1552.00,0.00,30)
,('376','2019-08-01','2023-10-01',1600.00,0.00,30)
,('376','2023-09-15','2023-09-01',1600.00,0.00,30)
,('376','2023-09-15','2023-10-01',1600.00,0.00,30)
,('376','2023-09-15','2023-11-01',1600.00,0.00,30)
,('376','2023-09-15','2023-12-01',1600.00,0.00,30)
,('376','2023-09-15','2024-01-01',1600.00,0.00,30)
,('376','2023-09-15','2024-02-01',1600.00,0.00,28)
,('376','2023-09-15','2024-03-01',1600.00,0.00,30)
,('376','2023-09-15','2024-04-01',1600.00,112.20,30)
,('376','2023-09-15','2024-05-01',1600.00,0.00,30)
,('376','2023-09-15','2024-06-01',1600.00,119.89,30)
,('376','2023-09-15','2024-07-01',1600.00,0.00,30)
,('376','2023-09-15','2024-08-01',1680.00,216.85,30)
,('376','2023-09-15','2024-09-01',1780.00,0.00,30)
,('376','2023-09-15','2024-10-01',1969.00,0.00,30)
,('376','2023-09-15','2024-11-01',1680.00,210.53,30)
;
select * from ir_costs;


EID
CLMinitialDT
MonthYear
Lodging
others
DaysPartial


049
2018-07-03
2019-04-01
435.9800
0.0000
30

049
2018-07-03
2019-05-01
435.9800
0.0000
30

049
2018-07-03
2019-06-01
435.9800
0.0000
30

049
2018-07-03
2019-07-01
130.7900
0.0000
9

049
2018-07-03
2020-01-01
261.5900
0.0000
18

049
2018-07-03
2020-02-01
435.9800
0.0000
28

049
2018-07-03
2020-03-01
435.9800
0.0000
30

049
2018-07-03
2020-04-01
435.9800
0.0000
30

049
2018-07-03
2020-05-01
435.9800
0.0000
30

049
2018-07-03
2020-06-01
435.9800
0.0000
30

049
2018-07-03
2020-07-01
421.4500
0.0000
29

049
2021-05-21
2022-08-01
53.6000
0.0000
4

049
2021-05-21
2022-12-01
77.5800
0.0000
16

049
2021-05-21
2023-01-01
415.4600
0.0000
30

049
2021-05-21
2023-02-01
415.4600
0.0000
28

049
2021-05-21
2023-03-01
415.4600
0.0000
30

049
2021-05-21
2023-04-01
415.4600
0.0000
30

049
2021-05-21
2023-05-01
415.4600
0.0000
30

049
2021-05-21
2023-06-01
439.9700
0.0000
30

049
2021-05-21
2023-07-01
263.9800
0.0000
18

376
2019-08-01
2020-08-01
1500.0000
100.0000
30

376
2019-08-01
2020-09-01
1500.0000
0.0000
30

376
2019-08-01
2020-10-01
1500.0000
0.0000
30

376
2019-08-01
2020-11-01
1500.0000
0.0000
30

376
2019-08-01
2020-12-01
1500.0000
100.0000
30

376
2019-08-01
2021-01-01
1500.0000
0.0000
30

376
2019-08-01
2021-02-01
1500.0000
100.0000
28

376
2019-08-01
2021-03-01
1500.0000
0.0000
30

376
2019-08-01
2021-04-01
1500.0000
100.0000
30

376
2019-08-01
2021-05-01
1500.0000
0.0000
30

376
2019-08-01
2021-06-01
1500.0000
100.0000
30

376
2019-08-01
2021-07-01
1500.0000
100.0000
30

376
2019-08-01
2021-08-01
1500.0000
100.0000
30

376
2019-08-01
2021-09-01
1500.0000
100.0000
30

376
2019-08-01
2021-10-01
1500.0000
100.0000
30

376
2019-08-01
2021-11-01
1500.0000
100.0000
30

376
2019-08-01
2021-12-01
1500.0000
0.0000
30

376
2019-08-01
2022-01-01
22.0000
0.0000
30

376
2019-08-01
2022-02-01
1522.0000
0.0000
28

376
2019-08-01
2022-03-01
1522.0000
0.0000
30

376
2019-08-01
2022-04-01
1522.0000
0.0000
30

376
2019-08-01
2022-05-01
1522.0000
0.0000
30

376
2019-08-01
2022-06-01
1522.0000
0.0000
30

376
2019-08-01
2022-07-01
1522.0000
0.0000
30

376
2019-08-01
2022-08-01
1522.0000
0.0000
30

376
2019-08-01
2022-09-01
1522.0000
0.0000
30

376
2019-08-01
2022-10-01
1522.0000
0.0000
30

376
2019-08-01
2022-11-01
1522.0000
0.0000
30

376
2019-08-01
2022-12-01
1522.0000
0.0000
30

376
2019-08-01
2023-01-01
1522.0000
0.0000
30

376
2019-08-01
2023-02-01
1522.0000
0.0000
28

376
2019-08-01
2023-03-01
1522.0000
0.0000
30

376
2019-08-01
2023-04-01
1552.0000
0.0000
30

376
2019-08-01
2023-05-01
1552.0000
0.0000
30

376
2019-08-01
2023-06-01
1552.0000
0.0000
30

376
2019-08-01
2023-07-01
1552.0000
0.0000
30

376
2019-08-01
2023-08-01
1552.0000
0.0000
30

376
2019-08-01
2023-10-01
1600.0000
0.0000
30

376
2023-09-15
2023-09-01
1600.0000
0.0000
30

376
2023-09-15
2023-10-01
1600.0000
0.0000
30

376
2023-09-15
2023-11-01
1600.0000
0.0000
30

376
2023-09-15
2023-12-01
1600.0000
0.0000
30

376
2023-09-15
2024-01-01
1600.0000
0.0000
30

376
2023-09-15
2024-02-01
1600.0000
0.0000
28

376
2023-09-15
2024-03-01
1600.0000
0.0000
30

376
2023-09-15
2024-04-01
1600.0000
112.2000
30

376
2023-09-15
2024-05-01
1600.0000
0.0000
30

376
2023-09-15
2024-06-01
1600.0000
119.8900
30

376
2023-09-15
2024-07-01
1600.0000
0.0000
30

376
2023-09-15
2024-08-01
1680.0000
216.8500
30

376
2023-09-15
2024-09-01
1780.0000
0.0000
30

376
2023-09-15
2024-10-01
1969.0000
0.0000
30

376
2023-09-15
2024-11-01
1680.0000
210.5300
30


DECLARE @months smallint=48;
SELECT * 
FROM(
select *
  ,max(RunningMonth)over(partition by EID) maxRunningMonth
from(
select *
  ,count(case when isBreak=0 then coalesce(MonthGap,0) else 0 end)
      over(partition by EID,grN order by MonthYear) RunningMonth
  ,SUM(Lodging + coalesce(others,0.0)) 
      OVER (PARTITION BY EID,grN ORDER BY MonthYear) as RunningTotal
from(
select *
  ,sum(isBreak)
    over(partition by EID order by MonthYear) grN
from(
  SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,c.others
  --  ,SUM(Lodging + coalesce(others,0.0)) over(partition by EId,CLMinitialDt order by MonthYear) RunningTotal
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    ,case when datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )>6 then 1 
     else 0 end isBreak
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
                   ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
  FROM ir_Costs c 
)a
)b
)c
)d WHERE (maxRunningMonth >= @months OR @months IS NULL)


EID
CLMinitialDT
MonthYear
PrevMth
Lodging
others
Repeats1
MonthGap
isBreak
TotalMonths
grN
RunningMonth
RunningTotal
maxRunningMonth


376
2019-08-01
2020-08-01
null
1500.0000
100.0000
0
null
0
52
0
1
1600.0000
53

376
2019-08-01
2020-09-01
2020-08-01
1500.0000
0.0000
0
1
0
52
0
2
3100.0000
53

376
2019-08-01
2020-10-01
2020-09-01
1500.0000
0.0000
0
1
0
52
0
3
4600.0000
53

376
2019-08-01
2020-11-01
2020-10-01
1500.0000
0.0000
0
1
0
52
0
4
6100.0000
53

376
2019-08-01
2020-12-01
2020-11-01
1500.0000
100.0000
0
1
0
52
0
5
7700.0000
53

376
2019-08-01
2021-01-01
2020-12-01
1500.0000
0.0000
0
1
0
52
0
6
9200.0000
53

376
2019-08-01
2021-02-01
2021-01-01
1500.0000
100.0000
0
1
0
52
0
7
10800.0000
53

376
2019-08-01
2021-03-01
2021-02-01
1500.0000
0.0000
0
1
0
52
0
8
12300.0000
53

376
2019-08-01
2021-04-01
2021-03-01
1500.0000
100.0000
0
1
0
52
0
9
13900.0000
53

376
2019-08-01
2021-05-01
2021-04-01
1500.0000
0.0000
0
1
0
52
0
10
15400.0000
53

376
2019-08-01
2021-06-01
2021-05-01
1500.0000
100.0000
0
1
0
52
0
11
17000.0000
53

376
2019-08-01
2021-07-01
2021-06-01
1500.0000
100.0000
0
1
0
52
0
12
18600.0000
53

376
2019-08-01
2021-08-01
2021-07-01
1500.0000
100.0000
0
1
0
52
0
13
20200.0000
53

376
2019-08-01
2021-09-01
2021-08-01
1500.0000
100.0000
0
1
0
52
0
14
21800.0000
53

376
2019-08-01
2021-10-01
2021-09-01
1500.0000
100.0000
0
1
0
52
0
15
23400.0000
53

376
2019-08-01
2021-11-01
2021-10-01
1500.0000
100.0000
0
1
0
52
0
16
25000.0000
53

376
2019-08-01
2021-12-01
2021-11-01
1500.0000
0.0000
0
1
0
52
0
17
26500.0000
53

376
2019-08-01
2022-01-01
2021-12-01
22.0000
0.0000
0
1
0
52
0
18
26522.0000
53

376
2019-08-01
2022-02-01
2022-01-01
1522.0000
0.0000
0
1
0
52
0
19
28044.0000
53

376
2019-08-01
2022-03-01
2022-02-01
1522.0000
0.0000
0
1
0
52
0
20
29566.0000
53

376
2019-08-01
2022-04-01
2022-03-01
1522.0000
0.0000
0
1
0
52
0
21
31088.0000
53

376
2019-08-01
2022-05-01
2022-04-01
1522.0000
0.0000
0
1
0
52
0
22
32610.0000
53

376
2019-08-01
2022-06-01
2022-05-01
1522.0000
0.0000
0
1
0
52
0
23
34132.0000
53

376
2019-08-01
2022-07-01
2022-06-01
1522.0000
0.0000
0
1
0
52
0
24
35654.0000
53

376
2019-08-01
2022-08-01
2022-07-01
1522.0000
0.0000
0
1
0
52
0
25
37176.0000
53

376
2019-08-01
2022-09-01
2022-08-01
1522.0000
0.0000
0
1
0
52
0
26
38698.0000
53

376
2019-08-01
2022-10-01
2022-09-01
1522.0000
0.0000
0
1
0
52
0
27
40220.0000
53

376
2019-08-01
2022-11-01
2022-10-01
1522.0000
0.0000
0
1
0
52
0
28
41742.0000
53

376
2019-08-01
2022-12-01
2022-11-01
1522.0000
0.0000
0
1
0
52
0
29
43264.0000
53

376
2019-08-01
2023-01-01
2022-12-01
1522.0000
0.0000
0
1
0
52
0
30
44786.0000
53

376
2019-08-01
2023-02-01
2023-01-01
1522.0000
0.0000
0
1
0
52
0
31
46308.0000
53

376
2019-08-01
2023-03-01
2023-02-01
1522.0000
0.0000
0
1
0
52
0
32
47830.0000
53

376
2019-08-01
2023-04-01
2023-03-01
1552.0000
0.0000
0
1
0
52
0
33
49382.0000
53

376
2019-08-01
2023-05-01
2023-04-01
1552.0000
0.0000
0
1
0
52
0
34
50934.0000
53

376
2019-08-01
2023-06-01
2023-05-01
1552.0000
0.0000
0
1
0
52
0
35
52486.0000
53

376
2019-08-01
2023-07-01
2023-06-01
1552.0000
0.0000
0
1
0
52
0
36
54038.0000
53

376
2019-08-01
2023-08-01
2023-07-01
1552.0000
0.0000
0
1
0
52
0
37
55590.0000
53

376
2023-09-15
2023-09-01
2023-08-01
1600.0000
0.0000
0
1
0
52
0
38
57190.0000
53

376
2023-09-15
2023-10-01
2023-09-01
1600.0000
0.0000
1
1
0
52
0
40
60390.0000
53

376
2019-08-01
2023-10-01
2023-10-01
1600.0000
0.0000
1
0
0
52
0
40
60390.0000
53

376
2023-09-15
2023-11-01
2023-10-01
1600.0000
0.0000
0
1
0
52
0
41
61990.0000
53

376
2023-09-15
2023-12-01
2023-11-01
1600.0000
0.0000
0
1
0
52
0
42
63590.0000
53

376
2023-09-15
2024-01-01
2023-12-01
1600.0000
0.0000
0
1
0
52
0
43
65190.0000
53

376
2023-09-15
2024-02-01
2024-01-01
1600.0000
0.0000
0
1
0
52
0
44
66790.0000
53

376
2023-09-15
2024-03-01
2024-02-01
1600.0000
0.0000
0
1
0
52
0
45
68390.0000
53

376
2023-09-15
2024-04-01
2024-03-01
1600.0000
112.2000
0
1
0
52
0
46
70102.2000
53

376
2023-09-15
2024-05-01
2024-04-01
1600.0000
0.0000
0
1
0
52
0
47
71702.2000
53

376
2023-09-15
2024-06-01
2024-05-01
1600.0000
119.8900
0
1
0
52
0
48
73422.0900
53

376
2023-09-15
2024-07-01
2024-06-01
1600.0000
0.0000
0
1
0
52
0
49
75022.0900
53

376
2023-09-15
2024-08-01
2024-07-01
1680.0000
216.8500
0
1
0
52
0
50
76918.9400
53

376
2023-09-15
2024-09-01
2024-08-01
1780.0000
0.0000
0
1
0
52
0
51
78698.9400
53

376
2023-09-15
2024-10-01
2024-09-01
1969.0000
0.0000
0
1
0
52
0
52
80667.9400
53

376
2023-09-15
2024-11-01
2024-10-01
1680.0000
210.5300
0
1
0
52
0
53
82558.4700
53


select *
  ,max(RunningMonth)over(partition by EID,grN) maxRunningMonth
from(
select *
  ,sum(case when isBreak=0 then coalesce(MonthGap,0) else 0 end)
      over(partition by EID,grN order by MonthYear) RunningMonth
from(
select *
  ,sum(isBreak)
    over(partition by EID order by MonthYear) grN
from(
  SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,SUM(Lodging + coalesce(others,0.0))
         over(partition by EId,CLMinitialDt order by MonthYear) RunningTotal
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    ,case when datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )>6 then 1 
     else 0 end isBreak
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
                   ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
  FROM ir_Costs c 
)a
)b
)c


EID
CLMinitialDT
MonthYear
PrevMth
Lodging
RunningTotal
Repeats1
MonthGap
isBreak
TotalMonths
grN
RunningMonth
maxRunningMonth


049
2018-07-03
2019-04-01
null
435.9800
435.9800
0
null
0
52
0
0
15

049
2018-07-03
2019-05-01
2019-04-01
435.9800
871.9600
0
1
0
52
0
1
15

049
2018-07-03
2019-06-01
2019-05-01
435.9800
1307.9400
0
1
0
52
0
2
15

049
2018-07-03
2019-07-01
2019-06-01
130.7900
1438.7300
0
1
0
52
0
3
15

049
2018-07-03
2020-01-01
2019-07-01
261.5900
1700.3200
0
6
0
52
0
9
15

049
2018-07-03
2020-02-01
2020-01-01
435.9800
2136.3000
0
1
0
52
0
10
15

049
2018-07-03
2020-03-01
2020-02-01
435.9800
2572.2800
0
1
0
52
0
11
15

049
2018-07-03
2020-04-01
2020-03-01
435.9800
3008.2600
0
1
0
52
0
12
15

049
2018-07-03
2020-05-01
2020-04-01
435.9800
3444.2400
0
1
0
52
0
13
15

049
2018-07-03
2020-06-01
2020-05-01
435.9800
3880.2200
0
1
0
52
0
14
15

049
2018-07-03
2020-07-01
2020-06-01
421.4500
4301.6700
0
1
0
52
0
15
15

049
2021-05-21
2022-08-01
2020-07-01
53.6000
53.6000
0
25
1
52
1
0
11

049
2021-05-21
2022-12-01
2022-08-01
77.5800
131.1800
0
4
0
52
1
4
11

049
2021-05-21
2023-01-01
2022-12-01
415.4600
546.6400
0
1
0
52
1
5
11

049
2021-05-21
2023-02-01
2023-01-01
415.4600
962.1000
0
1
0
52
1
6
11

049
2021-05-21
2023-03-01
2023-02-01
415.4600
1377.5600
0
1
0
52
1
7
11

049
2021-05-21
2023-04-01
2023-03-01
415.4600
1793.0200
0
1
0
52
1
8
11

049
2021-05-21
2023-05-01
2023-04-01
415.4600
2208.4800
0
1
0
52
1
9
11

049
2021-05-21
2023-06-01
2023-05-01
439.9700
2648.4500
0
1
0
52
1
10
11

049
2021-05-21
2023-07-01
2023-06-01
263.9800
2912.4300
0
1
0
52
1
11
11

376
2019-08-01
2020-08-01
null
1500.0000
1600.0000
0
null
0
52
0
0
51

376
2019-08-01
2020-09-01
2020-08-01
1500.0000
3100.0000
0
1
0
52
0
1
51

376
2019-08-01
2020-10-01
2020-09-01
1500.0000
4600.0000
0
1
0
52
0
2
51

376
2019-08-01
2020-11-01
2020-10-01
1500.0000
6100.0000
0
1
0
52
0
3
51

376
2019-08-01
2020-12-01
2020-11-01
1500.0000
7700.0000
0
1
0
52
0
4
51

376
2019-08-01
2021-01-01
2020-12-01
1500.0000
9200.0000
0
1
0
52
0
5
51

376
2019-08-01
2021-02-01
2021-01-01
1500.0000
10800.0000
0
1
0
52
0
6
51

376
2019-08-01
2021-03-01
2021-02-01
1500.0000
12300.0000
0
1
0
52
0
7
51

376
2019-08-01
2021-04-01
2021-03-01
1500.0000
13900.0000
0
1
0
52
0
8
51

376
2019-08-01
2021-05-01
2021-04-01
1500.0000
15400.0000
0
1
0
52
0
9
51

376
2019-08-01
2021-06-01
2021-05-01
1500.0000
17000.0000
0
1
0
52
0
10
51

376
2019-08-01
2021-07-01
2021-06-01
1500.0000
18600.0000
0
1
0
52
0
11
51

376
2019-08-01
2021-08-01
2021-07-01
1500.0000
20200.0000
0
1
0
52
0
12
51

376
2019-08-01
2021-09-01
2021-08-01
1500.0000
21800.0000
0
1
0
52
0
13
51

376
2019-08-01
2021-10-01
2021-09-01
1500.0000
23400.0000
0
1
0
52
0
14
51

376
2019-08-01
2021-11-01
2021-10-01
1500.0000
25000.0000
0
1
0
52
0
15
51

376
2019-08-01
2021-12-01
2021-11-01
1500.0000
26500.0000
0
1
0
52
0
16
51

376
2019-08-01
2022-01-01
2021-12-01
22.0000
26522.0000
0
1
0
52
0
17
51

376
2019-08-01
2022-02-01
2022-01-01
1522.0000
28044.0000
0
1
0
52
0
18
51

376
2019-08-01
2022-03-01
2022-02-01
1522.0000
29566.0000
0
1
0
52
0
19
51

376
2019-08-01
2022-04-01
2022-03-01
1522.0000
31088.0000
0
1
0
52
0
20
51

376
2019-08-01
2022-05-01
2022-04-01
1522.0000
32610.0000
0
1
0
52
0
21
51

376
2019-08-01
2022-06-01
2022-05-01
1522.0000
34132.0000
0
1
0
52
0
22
51

376
2019-08-01
2022-07-01
2022-06-01
1522.0000
35654.0000
0
1
0
52
0
23
51

376
2019-08-01
2022-08-01
2022-07-01
1522.0000
37176.0000
0
1
0
52
0
24
51

376
2019-08-01
2022-09-01
2022-08-01
1522.0000
38698.0000
0
1
0
52
0
25
51

376
2019-08-01
2022-10-01
2022-09-01
1522.0000
40220.0000
0
1
0
52
0
26
51

376
2019-08-01
2022-11-01
2022-10-01
1522.0000
41742.0000
0
1
0
52
0
27
51

376
2019-08-01
2022-12-01
2022-11-01
1522.0000
43264.0000
0
1
0
52
0
28
51

376
2019-08-01
2023-01-01
2022-12-01
1522.0000
44786.0000
0
1
0
52
0
29
51

376
2019-08-01
2023-02-01
2023-01-01
1522.0000
46308.0000
0
1
0
52
0
30
51

376
2019-08-01
2023-03-01
2023-02-01
1522.0000
47830.0000
0
1
0
52
0
31
51

376
2019-08-01
2023-04-01
2023-03-01
1552.0000
49382.0000
0
1
0
52
0
32
51

376
2019-08-01
2023-05-01
2023-04-01
1552.0000
50934.0000
0
1
0
52
0
33
51

376
2019-08-01
2023-06-01
2023-05-01
1552.0000
52486.0000
0
1
0
52
0
34
51

376
2019-08-01
2023-07-01
2023-06-01
1552.0000
54038.0000
0
1
0
52
0
35
51

376
2019-08-01
2023-08-01
2023-07-01
1552.0000
55590.0000
0
1
0
52
0
36
51

376
2023-09-15
2023-09-01
2023-08-01
1600.0000
1600.0000
0
1
0
52
0
37
51

376
2023-09-15
2023-10-01
2023-09-01
1600.0000
3200.0000
1
1
0
52
0
38
51

376
2019-08-01
2023-10-01
2023-10-01
1600.0000
57190.0000
1
0
0
52
0
38
51

376
2023-09-15
2023-11-01
2023-10-01
1600.0000
4800.0000
0
1
0
52
0
39
51

376
2023-09-15
2023-12-01
2023-11-01
1600.0000
6400.0000
0
1
0
52
0
40
51

376
2023-09-15
2024-01-01
2023-12-01
1600.0000
8000.0000
0
1
0
52
0
41
51

376
2023-09-15
2024-02-01
2024-01-01
1600.0000
9600.0000
0
1
0
52
0
42
51

376
2023-09-15
2024-03-01
2024-02-01
1600.0000
11200.0000
0
1
0
52
0
43
51

376
2023-09-15
2024-04-01
2024-03-01
1600.0000
12912.2000
0
1
0
52
0
44
51

376
2023-09-15
2024-05-01
2024-04-01
1600.0000
14512.2000
0
1
0
52
0
45
51

376
2023-09-15
2024-06-01
2024-05-01
1600.0000
16232.0900
0
1
0
52
0
46
51

376
2023-09-15
2024-07-01
2024-06-01
1600.0000
17832.0900
0
1
0
52
0
47
51

376
2023-09-15
2024-08-01
2024-07-01
1680.0000
19728.9400
0
1
0
52
0
48
51

376
2023-09-15
2024-09-01
2024-08-01
1780.0000
21508.9400
0
1
0
52
0
49
51

376
2023-09-15
2024-10-01
2024-09-01
1969.0000
23477.9400
0
1
0
52
0
50
51

376
2023-09-15
2024-11-01
2024-10-01
1680.0000
25368.4700
0
1
0
52
0
51
51


select *
  ,sum(coalesce(MonthGap,0))
    over(partition by EID,grN order by MonthYear) RunningMonth
from(
select *
  ,sum(isBreack)
    over(partition by EID order by MonthYear) grN
from(
  SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,SUM(Lodging + coalesce(others,0.0))
         over(partition by EId,CLMinitialDt order by MonthYear) RunningTotal
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    ,case when datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )>6 then 1 
     else 0 end isBreack
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
                   ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
  FROM ir_Costs c 
)a
)b


EID
CLMinitialDT
MonthYear
PrevMth
Lodging
RunningTotal
Repeats1
MonthGap
isBreack
TotalMonths
grN
RunningMonth


049
2018-07-03
2019-04-01
null
435.9800
435.9800
0
null
0
52
0
0

049
2018-07-03
2019-05-01
2019-04-01
435.9800
871.9600
0
1
0
52
0
1

049
2018-07-03
2019-06-01
2019-05-01
435.9800
1307.9400
0
1
0
52
0
2

049
2018-07-03
2019-07-01
2019-06-01
130.7900
1438.7300
0
1
0
52
0
3

049
2018-07-03
2020-01-01
2019-07-01
261.5900
1700.3200
0
6
0
52
0
9

049
2018-07-03
2020-02-01
2020-01-01
435.9800
2136.3000
0
1
0
52
0
10

049
2018-07-03
2020-03-01
2020-02-01
435.9800
2572.2800
0
1
0
52
0
11

049
2018-07-03
2020-04-01
2020-03-01
435.9800
3008.2600
0
1
0
52
0
12

049
2018-07-03
2020-05-01
2020-04-01
435.9800
3444.2400
0
1
0
52
0
13

049
2018-07-03
2020-06-01
2020-05-01
435.9800
3880.2200
0
1
0
52
0
14

049
2018-07-03
2020-07-01
2020-06-01
421.4500
4301.6700
0
1
0
52
0
15

049
2021-05-21
2022-08-01
2020-07-01
53.6000
53.6000
0
25
1
52
1
25

049
2021-05-21
2022-12-01
2022-08-01
77.5800
131.1800
0
4
0
52
1
29

049
2021-05-21
2023-01-01
2022-12-01
415.4600
546.6400
0
1
0
52
1
30

049
2021-05-21
2023-02-01
2023-01-01
415.4600
962.1000
0
1
0
52
1
31

049
2021-05-21
2023-03-01
2023-02-01
415.4600
1377.5600
0
1
0
52
1
32

049
2021-05-21
2023-04-01
2023-03-01
415.4600
1793.0200
0
1
0
52
1
33

049
2021-05-21
2023-05-01
2023-04-01
415.4600
2208.4800
0
1
0
52
1
34

049
2021-05-21
2023-06-01
2023-05-01
439.9700
2648.4500
0
1
0
52
1
35

049
2021-05-21
2023-07-01
2023-06-01
263.9800
2912.4300
0
1
0
52
1
36

376
2019-08-01
2020-08-01
null
1500.0000
1600.0000
0
null
0
52
0
0

376
2019-08-01
2020-09-01
2020-08-01
1500.0000
3100.0000
0
1
0
52
0
1

376
2019-08-01
2020-10-01
2020-09-01
1500.0000
4600.0000
0
1
0
52
0
2

376
2019-08-01
2020-11-01
2020-10-01
1500.0000
6100.0000
0
1
0
52
0
3

376
2019-08-01
2020-12-01
2020-11-01
1500.0000
7700.0000
0
1
0
52
0
4

376
2019-08-01
2021-01-01
2020-12-01
1500.0000
9200.0000
0
1
0
52
0
5

376
2019-08-01
2021-02-01
2021-01-01
1500.0000
10800.0000
0
1
0
52
0
6

376
2019-08-01
2021-03-01
2021-02-01
1500.0000
12300.0000
0
1
0
52
0
7

376
2019-08-01
2021-04-01
2021-03-01
1500.0000
13900.0000
0
1
0
52
0
8

376
2019-08-01
2021-05-01
2021-04-01
1500.0000
15400.0000
0
1
0
52
0
9

376
2019-08-01
2021-06-01
2021-05-01
1500.0000
17000.0000
0
1
0
52
0
10

376
2019-08-01
2021-07-01
2021-06-01
1500.0000
18600.0000
0
1
0
52
0
11

376
2019-08-01
2021-08-01
2021-07-01
1500.0000
20200.0000
0
1
0
52
0
12

376
2019-08-01
2021-09-01
2021-08-01
1500.0000
21800.0000
0
1
0
52
0
13

376
2019-08-01
2021-10-01
2021-09-01
1500.0000
23400.0000
0
1
0
52
0
14

376
2019-08-01
2021-11-01
2021-10-01
1500.0000
25000.0000
0
1
0
52
0
15

376
2019-08-01
2021-12-01
2021-11-01
1500.0000
26500.0000
0
1
0
52
0
16

376
2019-08-01
2022-01-01
2021-12-01
22.0000
26522.0000
0
1
0
52
0
17

376
2019-08-01
2022-02-01
2022-01-01
1522.0000
28044.0000
0
1
0
52
0
18

376
2019-08-01
2022-03-01
2022-02-01
1522.0000
29566.0000
0
1
0
52
0
19

376
2019-08-01
2022-04-01
2022-03-01
1522.0000
31088.0000
0
1
0
52
0
20

376
2019-08-01
2022-05-01
2022-04-01
1522.0000
32610.0000
0
1
0
52
0
21

376
2019-08-01
2022-06-01
2022-05-01
1522.0000
34132.0000
0
1
0
52
0
22

376
2019-08-01
2022-07-01
2022-06-01
1522.0000
35654.0000
0
1
0
52
0
23

376
2019-08-01
2022-08-01
2022-07-01
1522.0000
37176.0000
0
1
0
52
0
24

376
2019-08-01
2022-09-01
2022-08-01
1522.0000
38698.0000
0
1
0
52
0
25

376
2019-08-01
2022-10-01
2022-09-01
1522.0000
40220.0000
0
1
0
52
0
26

376
2019-08-01
2022-11-01
2022-10-01
1522.0000
41742.0000
0
1
0
52
0
27

376
2019-08-01
2022-12-01
2022-11-01
1522.0000
43264.0000
0
1
0
52
0
28

376
2019-08-01
2023-01-01
2022-12-01
1522.0000
44786.0000
0
1
0
52
0
29

376
2019-08-01
2023-02-01
2023-01-01
1522.0000
46308.0000
0
1
0
52
0
30

376
2019-08-01
2023-03-01
2023-02-01
1522.0000
47830.0000
0
1
0
52
0
31

376
2019-08-01
2023-04-01
2023-03-01
1552.0000
49382.0000
0
1
0
52
0
32

376
2019-08-01
2023-05-01
2023-04-01
1552.0000
50934.0000
0
1
0
52
0
33

376
2019-08-01
2023-06-01
2023-05-01
1552.0000
52486.0000
0
1
0
52
0
34

376
2019-08-01
2023-07-01
2023-06-01
1552.0000
54038.0000
0
1
0
52
0
35

376
2019-08-01
2023-08-01
2023-07-01
1552.0000
55590.0000
0
1
0
52
0
36

376
2023-09-15
2023-09-01
2023-08-01
1600.0000
1600.0000
0
1
0
52
0
37

376
2023-09-15
2023-10-01
2023-09-01
1600.0000
3200.0000
1
1
0
52
0
38

376
2019-08-01
2023-10-01
2023-10-01
1600.0000
57190.0000
1
0
0
52
0
38

376
2023-09-15
2023-11-01
2023-10-01
1600.0000
4800.0000
0
1
0
52
0
39

376
2023-09-15
2023-12-01
2023-11-01
1600.0000
6400.0000
0
1
0
52
0
40

376
2023-09-15
2024-01-01
2023-12-01
1600.0000
8000.0000
0
1
0
52
0
41

376
2023-09-15
2024-02-01
2024-01-01
1600.0000
9600.0000
0
1
0
52
0
42

376
2023-09-15
2024-03-01
2024-02-01
1600.0000
11200.0000
0
1
0
52
0
43

376
2023-09-15
2024-04-01
2024-03-01
1600.0000
12912.2000
0
1
0
52
0
44

376
2023-09-15
2024-05-01
2024-04-01
1600.0000
14512.2000
0
1
0
52
0
45

376
2023-09-15
2024-06-01
2024-05-01
1600.0000
16232.0900
0
1
0
52
0
46

376
2023-09-15
2024-07-01
2024-06-01
1600.0000
17832.0900
0
1
0
52
0
47

376
2023-09-15
2024-08-01
2024-07-01
1680.0000
19728.9400
0
1
0
52
0
48

376
2023-09-15
2024-09-01
2024-08-01
1780.0000
21508.9400
0
1
0
52
0
49

376
2023-09-15
2024-10-01
2024-09-01
1969.0000
23477.9400
0
1
0
52
0
50

376
2023-09-15
2024-11-01
2024-10-01
1680.0000
25368.4700
0
1
0
52
0
51


DECLARE @months smallint=48;
WITH Q1 AS ( SELECT c.EID
    ,c.CLMinitialDT 
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMonth
    ,c.Lodging
    ,(SELECT SUM(Lodging + others) 
      FROM ir_Costs d 
      WHERE d.MonthYear <= c.MonthYear 
         and d.EID=c.EID and d.CLMinitialDT =c.CLMinitialDT  
      GROUP BY d.EID, d.CLMinitialDT 
      ) AS RunningTotal 
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,NTILE(250) OVER (PARTITION BY c.EID ORDER BY c.monthyear,c.CLMinitialDT  ) AS RowSeq
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    --,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
      --             ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
     ,c.DaysPartial
  FROM ir_Costs c 
)
SELECT Q1.*,(COUNT(*) OVER (PARTITION BY Q1.EID)) AS CostRows, max(RowSeq) as TotalMonths 
FROM Q1 
GROUP BY EID,CLMinitialDT,MonthYear,PrevMonth,Lodging,RunningTotal,Repeats1,RowSeq,MonthGap,DaysPartial
ORDER BY EID, MonthYear



EID
CLMinitialDT
MonthYear
PrevMonth
Lodging
RunningTotal
Repeats1
RowSeq
MonthGap
DaysPartial
CostRows
TotalMonths


049
2018-07-03
2019-04-01
null
435.9800
435.9800
0
1
null
30
20
1

049
2018-07-03
2019-05-01
2019-04-01
435.9800
871.9600
0
2
1
30
20
2

049
2018-07-03
2019-06-01
2019-05-01
435.9800
1307.9400
0
3
1
30
20
3

049
2018-07-03
2019-07-01
2019-06-01
130.7900
1438.7300
0
4
1
9
20
4

049
2018-07-03
2020-01-01
2019-07-01
261.5900
1700.3200
0
5
6
18
20
5

049
2018-07-03
2020-02-01
2020-01-01
435.9800
2136.3000
0
6
1
28
20
6

049
2018-07-03
2020-03-01
2020-02-01
435.9800
2572.2800
0
7
1
30
20
7

049
2018-07-03
2020-04-01
2020-03-01
435.9800
3008.2600
0
8
1
30
20
8

049
2018-07-03
2020-05-01
2020-04-01
435.9800
3444.2400
0
9
1
30
20
9

049
2018-07-03
2020-06-01
2020-05-01
435.9800
3880.2200
0
10
1
30
20
10

049
2018-07-03
2020-07-01
2020-06-01
421.4500
4301.6700
0
11
1
29
20
11

049
2021-05-21
2022-08-01
2020-07-01
53.6000
53.6000
0
12
25
4
20
12

049
2021-05-21
2022-12-01
2022-08-01
77.5800
131.1800
0
13
4
16
20
13

049
2021-05-21
2023-01-01
2022-12-01
415.4600
546.6400
0
14
1
30
20
14

049
2021-05-21
2023-02-01
2023-01-01
415.4600
962.1000
0
15
1
28
20
15

049
2021-05-21
2023-03-01
2023-02-01
415.4600
1377.5600
0
16
1
30
20
16

049
2021-05-21
2023-04-01
2023-03-01
415.4600
1793.0200
0
17
1
30
20
17

049
2021-05-21
2023-05-01
2023-04-01
415.4600
2208.4800
0
18
1
30
20
18

049
2021-05-21
2023-06-01
2023-05-01
439.9700
2648.4500
0
19
1
30
20
19

049
2021-05-21
2023-07-01
2023-06-01
263.9800
2912.4300
0
20
1
18
20
20

376
2019-08-01
2020-08-01
null
1500.0000
1600.0000
0
1
null
30
53
1

376
2019-08-01
2020-09-01
2020-08-01
1500.0000
3100.0000
0
2
1
30
53
2

376
2019-08-01
2020-10-01
2020-09-01
1500.0000
4600.0000
0
3
1
30
53
3

376
2019-08-01
2020-11-01
2020-10-01
1500.0000
6100.0000
0
4
1
30
53
4

376
2019-08-01
2020-12-01
2020-11-01
1500.0000
7700.0000
0
5
1
30
53
5

376
2019-08-01
2021-01-01
2020-12-01
1500.0000
9200.0000
0
6
1
30
53
6

376
2019-08-01
2021-02-01
2021-01-01
1500.0000
10800.0000
0
7
1
28
53
7

376
2019-08-01
2021-03-01
2021-02-01
1500.0000
12300.0000
0
8
1
30
53
8

376
2019-08-01
2021-04-01
2021-03-01
1500.0000
13900.0000
0
9
1
30
53
9

376
2019-08-01
2021-05-01
2021-04-01
1500.0000
15400.0000
0
10
1
30
53
10

376
2019-08-01
2021-06-01
2021-05-01
1500.0000
17000.0000
0
11
1
30
53
11

376
2019-08-01
2021-07-01
2021-06-01
1500.0000
18600.0000
0
12
1
30
53
12

376
2019-08-01
2021-08-01
2021-07-01
1500.0000
20200.0000
0
13
1
30
53
13

376
2019-08-01
2021-09-01
2021-08-01
1500.0000
21800.0000
0
14
1
30
53
14

376
2019-08-01
2021-10-01
2021-09-01
1500.0000
23400.0000
0
15
1
30
53
15

376
2019-08-01
2021-11-01
2021-10-01
1500.0000
25000.0000
0
16
1
30
53
16

376
2019-08-01
2021-12-01
2021-11-01
1500.0000
26500.0000
0
17
1
30
53
17

376
2019-08-01
2022-01-01
2021-12-01
22.0000
26522.0000
0
18
1
30
53
18

376
2019-08-01
2022-02-01
2022-01-01
1522.0000
28044.0000
0
19
1
28
53
19

376
2019-08-01
2022-03-01
2022-02-01
1522.0000
29566.0000
0
20
1
30
53
20

376
2019-08-01
2022-04-01
2022-03-01
1522.0000
31088.0000
0
21
1
30
53
21

376
2019-08-01
2022-05-01
2022-04-01
1522.0000
32610.0000
0
22
1
30
53
22

376
2019-08-01
2022-06-01
2022-05-01
1522.0000
34132.0000
0
23
1
30
53
23

376
2019-08-01
2022-07-01
2022-06-01
1522.0000
35654.0000
0
24
1
30
53
24

376
2019-08-01
2022-08-01
2022-07-01
1522.0000
37176.0000
0
25
1
30
53
25

376
2019-08-01
2022-09-01
2022-08-01
1522.0000
38698.0000
0
26
1
30
53
26

376
2019-08-01
2022-10-01
2022-09-01
1522.0000
40220.0000
0
27
1
30
53
27

376
2019-08-01
2022-11-01
2022-10-01
1522.0000
41742.0000
0
28
1
30
53
28

376
2019-08-01
2022-12-01
2022-11-01
1522.0000
43264.0000
0
29
1
30
53
29

376
2019-08-01
2023-01-01
2022-12-01
1522.0000
44786.0000
0
30
1
30
53
30

376
2019-08-01
2023-02-01
2023-01-01
1522.0000
46308.0000
0
31
1
28
53
31

376
2019-08-01
2023-03-01
2023-02-01
1522.0000
47830.0000
0
32
1
30
53
32

376
2019-08-01
2023-04-01
2023-03-01
1552.0000
49382.0000
0
33
1
30
53
33

376
2019-08-01
2023-05-01
2023-04-01
1552.0000
50934.0000
0
34
1
30
53
34

376
2019-08-01
2023-06-01
2023-05-01
1552.0000
52486.0000
0
35
1
30
53
35

376
2019-08-01
2023-07-01
2023-06-01
1552.0000
54038.0000
0
36
1
30
53
36

376
2019-08-01
2023-08-01
2023-07-01
1552.0000
55590.0000
0
37
1
30
53
37

376
2023-09-15
2023-09-01
2023-08-01
1600.0000
1600.0000
0
38
1
30
53
38

376
2019-08-01
2023-10-01
2023-10-01
1600.0000
57190.0000
1
39
0
30
53
39

376
2023-09-15
2023-10-01
2023-09-01
1600.0000
3200.0000
1
40
1
30
53
40

376
2023-09-15
2023-11-01
2023-10-01
1600.0000
4800.0000
0
41
1
30
53
41

376
2023-09-15
2023-12-01
2023-11-01
1600.0000
6400.0000
0
42
1
30
53
42

376
2023-09-15
2024-01-01
2023-12-01
1600.0000
8000.0000
0
43
1
30
53
43

376
2023-09-15
2024-02-01
2024-01-01
1600.0000
9600.0000
0
44
1
28
53
44

376
2023-09-15
2024-03-01
2024-02-01
1600.0000
11200.0000
0
45
1
30
53
45

376
2023-09-15
2024-04-01
2024-03-01
1600.0000
12912.2000
0
46
1
30
53
46

376
2023-09-15
2024-05-01
2024-04-01
1600.0000
14512.2000
0
47
1
30
53
47

376
2023-09-15
2024-06-01
2024-05-01
1600.0000
16232.0900
0
48
1
30
53
48

376
2023-09-15
2024-07-01
2024-06-01
1600.0000
17832.0900
0
49
1
30
53
49

376
2023-09-15
2024-08-01
2024-07-01
1680.0000
19728.9400
0
50
1
30
53
50

376
2023-09-15
2024-09-01
2024-08-01
1780.0000
21508.9400
0
51
1
30
53
51

376
2023-09-15
2024-10-01
2024-09-01
1969.0000
23477.9400
0
52
1
30
53
52

376
2023-09-15
2024-11-01
2024-10-01
1680.0000
25368.4700
0
53
1
30
53
53

EID	CLMinitialDT	MonthYear	Lodging	others	DaysPartial
049	2018-07-03	2019-04-01	435.9800	0.0000	30
049	2018-07-03	2019-05-01	435.9800	0.0000	30
049	2018-07-03	2019-06-01	435.9800	0.0000	30
049	2018-07-03	2019-07-01	130.7900	0.0000	9
049	2018-07-03	2020-01-01	261.5900	0.0000	18
049	2018-07-03	2020-02-01	435.9800	0.0000	28
049	2018-07-03	2020-03-01	435.9800	0.0000	30
049	2018-07-03	2020-04-01	435.9800	0.0000	30
049	2018-07-03	2020-05-01	435.9800	0.0000	30
049	2018-07-03	2020-06-01	435.9800	0.0000	30
049	2018-07-03	2020-07-01	421.4500	0.0000	29
049	2021-05-21	2022-08-01	53.6000	0.0000	4
049	2021-05-21	2022-12-01	77.5800	0.0000	16
049	2021-05-21	2023-01-01	415.4600	0.0000	30
049	2021-05-21	2023-02-01	415.4600	0.0000	28
049	2021-05-21	2023-03-01	415.4600	0.0000	30
049	2021-05-21	2023-04-01	415.4600	0.0000	30
049	2021-05-21	2023-05-01	415.4600	0.0000	30
049	2021-05-21	2023-06-01	439.9700	0.0000	30
049	2021-05-21	2023-07-01	263.9800	0.0000	18
376	2019-08-01	2020-08-01	1500.0000	100.0000	30
376	2019-08-01	2020-09-01	1500.0000	0.0000	30
376	2019-08-01	2020-10-01	1500.0000	0.0000	30
376	2019-08-01	2020-11-01	1500.0000	0.0000	30
376	2019-08-01	2020-12-01	1500.0000	100.0000	30
376	2019-08-01	2021-01-01	1500.0000	0.0000	30
376	2019-08-01	2021-02-01	1500.0000	100.0000	28
376	2019-08-01	2021-03-01	1500.0000	0.0000	30
376	2019-08-01	2021-04-01	1500.0000	100.0000	30
376	2019-08-01	2021-05-01	1500.0000	0.0000	30
376	2019-08-01	2021-06-01	1500.0000	100.0000	30
376	2019-08-01	2021-07-01	1500.0000	100.0000	30
376	2019-08-01	2021-08-01	1500.0000	100.0000	30
376	2019-08-01	2021-09-01	1500.0000	100.0000	30
376	2019-08-01	2021-10-01	1500.0000	100.0000	30
376	2019-08-01	2021-11-01	1500.0000	100.0000	30
376	2019-08-01	2021-12-01	1500.0000	0.0000	30
376	2019-08-01	2022-01-01	22.0000	0.0000	30
376	2019-08-01	2022-02-01	1522.0000	0.0000	28
376	2019-08-01	2022-03-01	1522.0000	0.0000	30
376	2019-08-01	2022-04-01	1522.0000	0.0000	30
376	2019-08-01	2022-05-01	1522.0000	0.0000	30
376	2019-08-01	2022-06-01	1522.0000	0.0000	30
376	2019-08-01	2022-07-01	1522.0000	0.0000	30
376	2019-08-01	2022-08-01	1522.0000	0.0000	30
376	2019-08-01	2022-09-01	1522.0000	0.0000	30
376	2019-08-01	2022-10-01	1522.0000	0.0000	30
376	2019-08-01	2022-11-01	1522.0000	0.0000	30
376	2019-08-01	2022-12-01	1522.0000	0.0000	30
376	2019-08-01	2023-01-01	1522.0000	0.0000	30
376	2019-08-01	2023-02-01	1522.0000	0.0000	28
376	2019-08-01	2023-03-01	1522.0000	0.0000	30
376	2019-08-01	2023-04-01	1552.0000	0.0000	30
376	2019-08-01	2023-05-01	1552.0000	0.0000	30
376	2019-08-01	2023-06-01	1552.0000	0.0000	30
376	2019-08-01	2023-07-01	1552.0000	0.0000	30
376	2019-08-01	2023-08-01	1552.0000	0.0000	30
376	2019-08-01	2023-10-01	1600.0000	0.0000	30
376	2023-09-15	2023-09-01	1600.0000	0.0000	30
376	2023-09-15	2023-10-01	1600.0000	0.0000	30
376	2023-09-15	2023-11-01	1600.0000	0.0000	30
376	2023-09-15	2023-12-01	1600.0000	0.0000	30
376	2023-09-15	2024-01-01	1600.0000	0.0000	30
376	2023-09-15	2024-02-01	1600.0000	0.0000	28
376	2023-09-15	2024-03-01	1600.0000	0.0000	30
376	2023-09-15	2024-04-01	1600.0000	112.2000	30
376	2023-09-15	2024-05-01	1600.0000	0.0000	30
376	2023-09-15	2024-06-01	1600.0000	119.8900	30
376	2023-09-15	2024-07-01	1600.0000	0.0000	30
376	2023-09-15	2024-08-01	1680.0000	216.8500	30
376	2023-09-15	2024-09-01	1780.0000	0.0000	30
376	2023-09-15	2024-10-01	1969.0000	0.0000	30
376	2023-09-15	2024-11-01	1680.0000	210.5300	30

EID	CLMinitialDT	MonthYear	PrevMth	Lodging	others	Repeats1	MonthGap	TotalMonths	RunningMonth	RunningTotal	maxRunningMonth
376	2019-08-01	2020-08-01	null	1500.0000	100.0000	0	null	52	1	1600.0000	53
376	2019-08-01	2020-09-01	2020-08-01	1500.0000	0.0000	0	1	52	2	3100.0000	53
376	2019-08-01	2020-10-01	2020-09-01	1500.0000	0.0000	0	1	52	3	4600.0000	53
376	2019-08-01	2020-11-01	2020-10-01	1500.0000	0.0000	0	1	52	4	6100.0000	53
376	2019-08-01	2020-12-01	2020-11-01	1500.0000	100.0000	0	1	52	5	7700.0000	53
376	2019-08-01	2021-01-01	2020-12-01	1500.0000	0.0000	0	1	52	6	9200.0000	53
376	2019-08-01	2021-02-01	2021-01-01	1500.0000	100.0000	0	1	52	7	10800.0000	53
376	2019-08-01	2021-03-01	2021-02-01	1500.0000	0.0000	0	1	52	8	12300.0000	53
376	2019-08-01	2021-04-01	2021-03-01	1500.0000	100.0000	0	1	52	9	13900.0000	53
376	2019-08-01	2021-05-01	2021-04-01	1500.0000	0.0000	0	1	52	10	15400.0000	53
376	2019-08-01	2021-06-01	2021-05-01	1500.0000	100.0000	0	1	52	11	17000.0000	53
376	2019-08-01	2021-07-01	2021-06-01	1500.0000	100.0000	0	1	52	12	18600.0000	53
376	2019-08-01	2021-08-01	2021-07-01	1500.0000	100.0000	0	1	52	13	20200.0000	53
376	2019-08-01	2021-09-01	2021-08-01	1500.0000	100.0000	0	1	52	14	21800.0000	53
376	2019-08-01	2021-10-01	2021-09-01	1500.0000	100.0000	0	1	52	15	23400.0000	53
376	2019-08-01	2021-11-01	2021-10-01	1500.0000	100.0000	0	1	52	16	25000.0000	53
376	2019-08-01	2021-12-01	2021-11-01	1500.0000	0.0000	0	1	52	17	26500.0000	53
376	2019-08-01	2022-01-01	2021-12-01	22.0000	0.0000	0	1	52	18	26522.0000	53
376	2019-08-01	2022-02-01	2022-01-01	1522.0000	0.0000	0	1	52	19	28044.0000	53
376	2019-08-01	2022-03-01	2022-02-01	1522.0000	0.0000	0	1	52	20	29566.0000	53
376	2019-08-01	2022-04-01	2022-03-01	1522.0000	0.0000	0	1	52	21	31088.0000	53
376	2019-08-01	2022-05-01	2022-04-01	1522.0000	0.0000	0	1	52	22	32610.0000	53
376	2019-08-01	2022-06-01	2022-05-01	1522.0000	0.0000	0	1	52	23	34132.0000	53
376	2019-08-01	2022-07-01	2022-06-01	1522.0000	0.0000	0	1	52	24	35654.0000	53
376	2019-08-01	2022-08-01	2022-07-01	1522.0000	0.0000	0	1	52	25	37176.0000	53
376	2019-08-01	2022-09-01	2022-08-01	1522.0000	0.0000	0	1	52	26	38698.0000	53
376	2019-08-01	2022-10-01	2022-09-01	1522.0000	0.0000	0	1	52	27	40220.0000	53
376	2019-08-01	2022-11-01	2022-10-01	1522.0000	0.0000	0	1	52	28	41742.0000	53
376	2019-08-01	2022-12-01	2022-11-01	1522.0000	0.0000	0	1	52	29	43264.0000	53
376	2019-08-01	2023-01-01	2022-12-01	1522.0000	0.0000	0	1	52	30	44786.0000	53
376	2019-08-01	2023-02-01	2023-01-01	1522.0000	0.0000	0	1	52	31	46308.0000	53
376	2019-08-01	2023-03-01	2023-02-01	1522.0000	0.0000	0	1	52	32	47830.0000	53
376	2019-08-01	2023-04-01	2023-03-01	1552.0000	0.0000	0	1	52	33	49382.0000	53
376	2019-08-01	2023-05-01	2023-04-01	1552.0000	0.0000	0	1	52	34	50934.0000	53
376	2019-08-01	2023-06-01	2023-05-01	1552.0000	0.0000	0	1	52	35	52486.0000	53
376	2019-08-01	2023-07-01	2023-06-01	1552.0000	0.0000	0	1	52	36	54038.0000	53
376	2019-08-01	2023-08-01	2023-07-01	1552.0000	0.0000	0	1	52	37	55590.0000	53
376	2023-09-15	2023-09-01	2023-08-01	1600.0000	0.0000	0	1	52	38	57190.0000	53
376	2023-09-15	2023-10-01	2023-09-01	1600.0000	0.0000	1	1	52	40	60390.0000	53
376	2019-08-01	2023-10-01	2023-10-01	1600.0000	0.0000	1	0	52	40	60390.0000	53
376	2023-09-15	2023-11-01	2023-10-01	1600.0000	0.0000	0	1	52	41	61990.0000	53
376	2023-09-15	2023-12-01	2023-11-01	1600.0000	0.0000	0	1	52	42	63590.0000	53
376	2023-09-15	2024-01-01	2023-12-01	1600.0000	0.0000	0	1	52	43	65190.0000	53
376	2023-09-15	2024-02-01	2024-01-01	1600.0000	0.0000	0	1	52	44	66790.0000	53
376	2023-09-15	2024-03-01	2024-02-01	1600.0000	0.0000	0	1	52	45	68390.0000	53
376	2023-09-15	2024-04-01	2024-03-01	1600.0000	112.2000	0	1	52	46	70102.2000	53
376	2023-09-15	2024-05-01	2024-04-01	1600.0000	0.0000	0	1	52	47	71702.2000	53
376	2023-09-15	2024-06-01	2024-05-01	1600.0000	119.8900	0	1	52	48	73422.0900	53
376	2023-09-15	2024-07-01	2024-06-01	1600.0000	0.0000	0	1	52	49	75022.0900	53
376	2023-09-15	2024-08-01	2024-07-01	1680.0000	216.8500	0	1	52	50	76918.9400	53
376	2023-09-15	2024-09-01	2024-08-01	1780.0000	0.0000	0	1	52	51	78698.9400	53
376	2023-09-15	2024-10-01	2024-09-01	1969.0000	0.0000	0	1	52	52	80667.9400	53
376	2023-09-15	2024-11-01	2024-10-01	1680.0000	210.5300	0	1	52	53	82558.4700	53