The internal logic values are in the script

[wood]

Hello, look at the following table. How to automatically generate the START DATE and END DATE fields in the script?

The start and end dates are taken from RUN_DATE as the contract number and audit result change.

Contract NO.	Run_Date	Audit Result	START DATE	END DATE
3700000059598	2020/11/21	Normal
3700000059598	2020/11/22	Normal
3700000059598	2020/11/22	Normal	2020/11/21	2020/11/22
3700000059598	2020/11/23	Update Location
3700000059598	2020/11/24	Update Location	2020/11/23	2020/11/24
3700000059598	2020/11/25	Normal
3700000059598	2020/11/26	Normal	2020/11/25	2020/11/26
3700000055555	2020/11/21	Normal
3700000055555	2020/11/22	Normal	2020/11/21	2020/11/22

[Taoufiq_Zarra · Accepted solution]

@wood  May be this :

Data:

LOAD *,if(rowno()=1,1,if(peek([Contract NO.])=[Contract NO.] and [Audit Result]=peek([Audit Result]),peek(Idtmp),peek(Idtmp)+1)) as Idtmp INLINE [
    Contract NO., Run_Date, Audit Result
    3700000059598, 2020/11/21, Normal
    3700000059598, 2020/11/22, Normal
    3700000059598, 2020/11/22, Normal
    3700000059598, 2020/11/23, Update Location
    3700000059598, 2020/11/24, Update Location
    3700000059598, 2020/11/25, Normal
    3700000059598, 2020/11/26, Normal
    3700000055555, 2020/11/21, Normal
    3700000055555, 2020/11/22, Normal
];

output:
noconcatenate

load * resident Data;
left join load [Contract NO.],[Audit Result],Idtmp,Date(Min(Date#(Run_Date,'YYYY/MM/DD'))) as [START DATE],Date(Max(Date#(Run_Date,'YYYY/MM/DD'))) as [END DATE] resident Data group by [Contract NO.],[Audit Result],Idtmp;

drop table Data;
drop fields Idtmp;

output:

[wood]

thank you Zarra