get the middle date from sequence of dates

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

get the middle date from sequence of dates

Post by sal21 »

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

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

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

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: get the middle date from sequence of dates

Post by sal21 »

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 ...
ok in this case i know the way to fill array with recordset, but o to get the middle value from array?
possible?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

Can there be null values (blanks) in the array? Or is the date filled in in all records?
Best wishes,
Hans

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: get the middle date from sequence of dates

Post by sal21 »

HansV wrote:Can there be null values (blanks) in the array? Or is the date filled in in all records?
No null value, all items in to array are filled

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: get the middle date from sequence of dates

Post by Pat »

What happens if there are 4 distinct items in the list? Do you want the 2nd or the 3rd?

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

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

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: get the middle date from sequence of dates

Post by Pat »

I agree with what you say re median, but does he want the average of the middle 2 items.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

Perhaps Sal wants the median of the unique values in the list? He'll have to tell us.
Best wishes,
Hans

Pat
5StarLounger
Posts: 1148
Joined: 08 Feb 2010, 21:27

Re: get the middle date from sequence of dates

Post by Pat »

Fair comment, i'll just watch you at work.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

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:

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

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: get the middle date from sequence of dates

Post by HansV »

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

User avatar
sal21
PlatinumLounger
Posts: 4334
Joined: 26 Apr 2010, 17:36

Re: get the middle date from sequence of dates

Post by sal21 »

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
I think i ue this tips, i arrange in ADO.
:thankyou: GENIUS!