I have the following sample data:
IND ID value EFFECTIVE DT SYSDATE
8 A 19289 6/30/2017 8/16/2017 10:05
17 A 19289 6/30/2017 8/15/2017 14:25
26 A 19289 6/30/2017 8/14/2017 15:10
7 A 18155 3/31/2017 8/16/2017 10:05
16 A 18155 3/31/2017 8/15/2017 14:25
25 A 18155 3/31/2017 8/14/2017 15:10
6 A 21770 12/31/2016 8/16/2017 10:05
15 A 21770 12/31/2016 8/15/2017 14:25
24 A 21770 12/31/2016 8/14/2017 15:10
5 A 19226 9/30/2016 8/16/2017 10:05
14 A 19226 9/30/2016 8/15/2017 14:25
23 A 19226 9/30/2016 8/14/2017 15:10
4 A 20238 6/30/2016 8/16/2017 10:05
13 A 20238 6/30/2016 8/15/2017 14:25
22 A 20238 6/30/2016 8/14/2017 15:10
3 A 18684 3/31/2016 8/16/2017 10:05
12 A 18684 3/31/2016 8/15/2017 14:25
21 A 18684 3/31/2016 8/14/2017 15:10
2 A 22059 12/31/2015 8/16/2017 10:05
11 A 22059 12/31/2015 8/15/2017 14:25
20 A 22059 12/31/2015 8/14/2017 15:10
1 A 19280 9/30/2015 8/16/2017 10:05
10 A 19280 9/30/2015 8/15/2017 14:25
19 A 19280 9/30/2015 8/14/2017 15:10
0 A 20813 6/30/2015 8/16/2017 10:05
9 A 20813 6/30/2015 8/15/2017 14:25
18 A 20813 6/30/2015 8/14/2017 15:10
It is a set of data I collect every weekday (SYSDATE is a timestamp).
I want to generate a df with only the rows stamped with either of the two most recent timestamp values every day.
So I'm looking to get this (from much larger set with many timestamps) if I were to run the script today:
IND ID Value EFFECTIVE DT SYSDATE
8 A 19289 6/30/2017 8/16/2017 10:05
17 A 19289 6/30/2017 8/15/2017 14:25
7 A 18155 3/31/2017 8/16/2017 10:05
16 A 18155 3/31/2017 8/15/2017 14:25
6 A 21770 12/31/2016 8/16/2017 10:05
15 A 21770 12/31/2016 8/15/2017 14:25
5 A 19226 9/30/2016 8/16/2017 10:05
14 A 19226 9/30/2016 8/15/2017 14:25
4 A 20238 6/30/2016 8/16/2017 10:05
13 A 20238 6/30/2016 8/15/2017 14:25
3 A 18684 3/31/2016 8/16/2017 10:05
12 A 18684 3/31/2016 8/15/2017 14:25
2 A 22059 12/31/2015 8/16/2017 10:05
11 A 22059 12/31/2015 8/15/2017 14:25
1 A 19280 9/30/2015 8/16/2017 10:05
10 A 19280 9/30/2015 8/15/2017 14:25
0 A 20813 6/30/2015 8/16/2017 10:05
9 A 20813 6/30/2015 8/15/2017 14:25
I can't use datetimes because of weekends and holidays.
Suggestions?
Thanks in advance.