Code:
awk -F, '
function GTM(T,D) {cmd = "date +%Y-%m-%d -d\"" T D "days\""
cmd | getline X
close (cmd)
return X
}
NR == 1 {print
next
}
$2 != LAST &&
R {T = $0
$0 = LL
$7 = L
$8 = $18
$9 = 0
print
$0 = T
}
$7 > L && R {T = $0
$8 = GTM($7, " -1")
$7 = L
$9 = 0
print
$0 = T
}
1
{L = GTM($8," +1")
R = ($8 != $18)
LAST = $2
LL = $0
}
END {if (R) {$7 = L
$8 = $18
$9 = 0
print
}
}
' OFS="," file
COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18
C,1234,TEST,1,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2016-12-01,2017-11-30,0,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,1234,TEST,1,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,1234,TEST,1,AA,AAAA,2019-12-01,2020-11-30,0,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2016-12-01,2017-11-30,210365.77,AAA,P,4095224.45,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2017-12-01,2018-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2018-12-01,2019-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,5678,TEST,2,AA,AAAA,2019-12-01,2020-11-30,0,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2020-11-30
C,9999,TEST,3,AA,AAAA,2015-12-01,2016-11-30,190425.71,AAA,P,4305590.22,NULL,NULL,NULL,NULL,NULL,2019-11-30
C,9999,TEST,3,AA,AAAA,2016-12-01,2017-11-30,232393.82,AAA,P,3862830.63,NULL,NULL,NULL,NULL,NULL,2018-10-01
C,9999,TEST,3,AA,AAAA,2017-12-01,2018-11-30,256728.49,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2023-10-01
C,9999,TEST,3,AA,AAAA,2018-12-01,2023-10-01,0,AAA,P,3606102.14,NULL,NULL,NULL,NULL,NULL,2023-10-01