need a query that finds number of days in between several rows. Same col2 as of the row with the highest date.
input:
.
.
something like(output):
output table:
input:
Code:
table1:
mydata col2 mydate
"AAAA" A1 11/20/2013
"BBBB" B1 11/21/2014
"AAAA" A1 10/01/2013
"CCCC" C1 08/01/2016
"AAAA" A1 11/15/2013
"BBBB" B1 09/21/2014
"BBBB" B1 07/21/2014
"AAAA" A2 11/20/2011
"BBBB" B2 07/21/2012
"CCCC" C2 08/01/2010
.
something like(output):
output table:
Code:
mydata col2 mydate date2 dayes
-----------------------------------------------------------------
"AAAA" A1 11/20/2013 11/20/2013 0
"AAAA" A1 11/15/2013 11/20/2013 5
"AAAA" A1 10/01/2013 11/20/2013 41
"AAAA" A1 10/01/2013 11/15/2013 35
"BBBB" B1 11/21/2014 11/21/2014 0
"BBBB" B1 09/21/2014 11/21/2014 60
"BBBB" B1 07/21/2014 11/21/2014 90
"BBBB" B1 07/21/2014 09/21/2014 60