With a sql, please.
I have a list of dates in field DATE1.
I need to get the middle value from the list.
similar:
01/05/2014
01/05/2014
01/05/2014
11/05/2014
01/05/2014
08/05/2014
in this case 08/05/2014
Note:
i can have a duplicate dates
get the middle date from sequence of dates
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
There is no direct way to do this in SQL; you need to use VBA. Do you want to do this in Access itself, or in Excel, or ...
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: get the middle date from sequence of dates
ok in this case i know the way to fill array with recordset, but o to get the middle value from array?HansV wrote:There is no direct way to do this in SQL; you need to use VBA. Do you want to do this in Access itself, or in Excel, or ...
possible?
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
Can there be null values (blanks) in the array? Or is the date filled in in all records?
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: get the middle date from sequence of dates
No null value, all items in to array are filledHansV wrote:Can there be null values (blanks) in the array? Or is the date filled in in all records?
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: get the middle date from sequence of dates
What happens if there are 4 distinct items in the list? Do you want the 2nd or the 3rd?
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
The convention for computing the median is to take the average of the middle two items if the total number of items is even.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: get the middle date from sequence of dates
I agree with what you say re median, but does he want the average of the middle 2 items.
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
Perhaps Sal wants the median of the unique values in the list? He'll have to tell us.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: get the middle date from sequence of dates
Fair comment, i'll just watch you at work.
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
Lacking further information:
- Open a recordset on a SQL string like this (substitute the correct table name):
"SELECT DISTINCT DATE1 FROM TableName ORDER BY DATE1"
- Use GetRows to get the records into an array, say arrData
- Use code like this to get the median value:
- Open a recordset on a SQL string like this (substitute the correct table name):
"SELECT DISTINCT DATE1 FROM TableName ORDER BY DATE1"
- Use GetRows to get the records into an array, say arrData
- Use code like this to get the median value:
Code: Select all
Dim n As Long
Dim d As Date
n = UBound(arrData, 2)
If n Mod 2 = 0 Then
d = arrData(0, n / 2)
Else
d = (arrData(0, (n - 1) / 2) + arrdata(0, (n + 1) / 2)) / 2
End If
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78594
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: get the middle date from sequence of dates
And another method (this version uses DAO, but it can easily be adapted for ADO):
Code: Select all
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim d1 As Date
Dim d2 As Date
Dim d As Date
Set dbs = CurrentDb ' or open an external database
strSQL = "SELECT Max(DATE1) AS d1 FROM " & _
"(SELECT DISTINCT TOP 50 PERCENT DATE1 FROM TableName ORDER BY DATE1)"
Set rst = dbs.OpenRecordset(strSQL)
d1 = rst!d1
rst.Close
strSQL = "SELECT Min(DATE1) AS d2 FROM " & _
"(SELECT DISTINCT TOP 50 PERCENT DATE1 FROM TableName ORDER BY DATE1 DESC)"
Set rst = dbs.OpenRecordset(strSQL)
d2 = rst!d2
rst.Close
d = (d1 + d2) / 2
Best wishes,
Hans
Hans
-
- PlatinumLounger
- Posts: 4368
- Joined: 26 Apr 2010, 17:36
Re: get the middle date from sequence of dates
I think i ue this tips, i arrange in ADO.HansV wrote:And another method (this version uses DAO, but it can easily be adapted for ADO):
Code: Select all
Dim strSQL As String Dim dbs As DAO.Database Dim rst As DAO.Recordset Dim d1 As Date Dim d2 As Date Dim d As Date Set dbs = CurrentDb ' or open an external database strSQL = "SELECT Max(DATE1) AS d1 FROM " & _ "(SELECT DISTINCT TOP 50 PERCENT DATE1 FROM TableName ORDER BY DATE1)" Set rst = dbs.OpenRecordset(strSQL) d1 = rst!d1 rst.Close strSQL = "SELECT Min(DATE1) AS d2 FROM " & _ "(SELECT DISTINCT TOP 50 PERCENT DATE1 FROM TableName ORDER BY DATE1 DESC)" Set rst = dbs.OpenRecordset(strSQL) d2 = rst!d2 rst.Close d = (d1 + d2) / 2
GENIUS!