Total Days Based on Status

[ch_riadh]

HI,

I have this table:

AccountNo	StartDate	EndDate	Status	Days
1002	31/01/2020	03/02/2020	Exceed	4
1002	04/02/2020	10/03/2020	Exceed	36
1002	11/03/2020	11/03/2020	Exceed	1
1002	12/03/2020	16/03/2020	Exceed	5
1002	17/03/2020	17/03/2020	Exceed	1
1002	18/03/2020	06/04/2020	Normal	20
1002	07/04/2020	20/04/2020	Exceed	14
1002	21/04/2020	22/04/2020	Exceed	2
1002	23/04/2020	28/04/2020	Exceed	6
1002	29/04/2020	02/05/2020	Exceed	4
1002	03/05/2020	10/06/2020	Exceed	39
1002	11/06/2020	16/06/2020	Exceed	6
1002	17/06/2020	28/06/2020	Exceed	12
1002	29/06/2020	07/07/2020	Normal	9
1002	08/07/2020	04/08/2020	Normal	28
1002	05/08/2020	19/08/2020	Exceed	15

And i want to display this table:

AccountNo	StartDate	EndDate	Status	CumulDays
1002	31/01/2020	17/03/2020	Exceed	47
1002	18/03/2020	06/04/2020	Normal	20
1002	07/04/2020	28/06/2020	Exceed	83
1002	29/06/2020	04/08/2020	Normal	37
1002	05/08/2020	19/08/2020	Exceed	15

Thank You

[Taoufiq_Zarra]

Maye be like :

Input:

LOAD * INLINE [
    AccountNo, StartDate, EndDate, Status, Days
    1002, 31/01/2020, 03/02/2020, Exceed, 4
    1002, 04/02/2020, 10/03/2020, Exceed, 36
    1002, 11/03/2020, 11/03/2020, Exceed, 1
    1002, 12/03/2020, 16/03/2020, Exceed, 5
    1002, 17/03/2020, 17/03/2020, Exceed, 1
    1002, 18/03/2020, 06/04/2020, Normal, 20
    1002, 07/04/2020, 20/04/2020, Exceed, 14
    1002, 21/04/2020, 22/04/2020, Exceed, 2
    1002, 23/04/2020, 28/04/2020, Exceed, 6
    1002, 29/04/2020, 02/05/2020, Exceed, 4
    1002, 03/05/2020, 10/06/2020, Exceed, 39
    1002, 11/06/2020, 16/06/2020, Exceed, 6
    1002, 17/06/2020, 28/06/2020, Exceed, 12
    1002, 29/06/2020, 07/07/2020, Normal, 9
    1002, 08/07/2020, 04/08/2020, Normal, 28
    1002, 05/08/2020, 19/08/2020, Exceed, 15
];

Tmp:
noconcatenate

load * resident Input order by AccountNo,StartDate,EndDate;

drop table Input;


Data:
noconcatenate

load *,if(rowno()=1,1,if(peek(Status)=Status and AccountNo=peek(AccountNo),peek(IDtmp),peek(IDtmp)+1)) as IDtmp resident Tmp;

drop table Tmp;

Final:
noconcatenate

load AccountNo,Date(min(StartDate)) as StartDate,Date(max(EndDate)) as EndDate, Status,IDtmp,sum(Days) as CumulDays resident Data group by AccountNo,Status,IDtmp;

drop table Data;

output :

you can remove the "Tmp:" part  if the input table is already sorted.

[lironbaram]

hi 

this script is an example to the calculations you need to do to get the results 

Table1:
load * inline [
AccountNo,	StartDate,	EndDate,	Status,	Days
1002,	31/01/2020,	03/02/2020,	Exceed,	4
1002,	04/02/2020,	10/03/2020,	Exceed,	36
1002,	11/03/2020,	11/03/2020,	Exceed,	1
1002,	12/03/2020,	16/03/2020,	Exceed,	5
1002,	17/03/2020,	17/03/2020,	Exceed,	1
1002,	18/03/2020,	06/04/2020,	Normal,	20
1002,	07/04/2020,	20/04/2020,	Exceed,	14
1002,	21/04/2020,	22/04/2020,	Exceed,	2
1002,	23/04/2020,	28/04/2020,	Exceed,	6
1002,	29/04/2020,	02/05/2020,	Exceed,	4
1002,	03/05/2020,	10/06/2020,	Exceed,	39
1002,	11/06/2020,	16/06/2020,	Exceed,	6
1002,	17/06/2020,	28/06/2020,	Exceed,	12
1002,	29/06/2020,	07/07/2020,	Normal,	9
1002,	08/07/2020,	04/08/2020,	Normal,	28
1002,	05/08/2020,	19/08/2020,	Exceed,	15
];


Table2:
load *,
     if(Previous(AccountNo)=AccountNo and Previous(Status)=Status, RangeSum(peek('accDays'),Days),Days) as accDays,
     if(Previous(AccountNo)=AccountNo and Previous(Status)=Status, peek('counter'),rangesum(peek('counter'),1)) as counter
Resident Table1
order by AccountNo,StartDate;

drop table Table1;

Table3:
load AccountNo,
     min(StartDate) as StartDate,
     max(EndDate) as EndDate,
     Status,
     max(accDays) as accDays
Resident Table2
group by AccountNo,Status,counter;


drop Table Table2;