intervals among rows (1 Viewer)

syodb

Registered User.
Local time
Today, 07:53
Joined
Sep 7, 2017
Messages
27
need a query that finds number of days in between several rows. Same col2 as of the row with the highest date.


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
 

plog

Banishment Pending
Local time
Today, 09:53
Joined
May 11, 2011
Messages
11,611
That doesn't quite make sense. Explain your example results to me:

1. Why isn't there a record with Col2=A2 in the result?

2. There are 3 Col2=A1 records in the initial data, but 4 Col2=A1 records in the results? How'd you end up with more in the result thatn what you started with?

3. How come all the Col2=A1 don't have the same date2 values? There's one with date2=11/15/2013.

Please explain the logic in why those 3 things happened, or if your expected results are incorrect, please correct them.
 

GinaWhipp

AWF VIP
Local time
Today, 10:53
Joined
Jun 21, 2011
Messages
5,901
Well it appears to me you are sorting on Col2 and then Days. So add a

dtDays: [Date2]-[MyDate]

Column to your Query and then sort ASC on Col2 and dtDays.
 

Micron

AWF VIP
Local time
Today, 10:53
Joined
Oct 20, 2018
Messages
3,476
I don't see it that way. Days is a calculation that is yet to be performed, so you can't sort on what' not there. I couldn't make sense of it either, but might be close now.
A totals query, count of col2 grouped by myData would be needed to start with. That would weed out where there's only single values for Col2, such as B2. The row pattern seems to be, for each of col2 that remains

2 values with the max date
1 value next oldest date (descending) and the max date. Repeat??
1 value next oldest date (which is the min date in the examples) and the max date
1 value min date and max(DateOlderThanMaxDate)
Maybe it's just coincidence that there is no repeat of line 2.
This would be a monster query if it can be done in one, or even one with subqueries, IMHO. Perhaps a set of UNION queries? It can probably be done with code.
 
Last edited:

Users who are viewing this thread

Top Bottom