HERE's a challenge, grouping by date field differences

[Bullsandbears123]

This is the challenge. I'm not able to do it, but some of you might me able to.

Using SQL to group dependent on date differences.

For example try to group all items that are within 5 days of each other in the same group.

e.g. raw data
date value
1/1/04 1
1/2/04 1
1/4/04 1
1/10/04 1
1/13/04 1
1/14/04 1
1/15/04 1
1/17/04 1
1/19/01 1
1/25/04 1
1/27/04 1
1/30/04 1
2/1/04 1


should group as
Group 1
1/1/04
1/2/04
1/4/04

Group 2
1/10/04
1/13/04
1/14/04
1/15/04
1/17/04
1/19/04
group 3
1/25/04
1/27/04
1/30/04
2/1/04

should display max(date), sum(value)

Any Ideas. I'd be very impressed with who ever has a solution.

THANK YOU.

 

[leishad]

Where are you creating the query? Which version of SQL (for Access, MSSQL, MySQL)?

 

[JonFer]

Save this SQL as query "qryDates" - it gives you each date and the next closest date:

Select Distinct DateField,
(Select Min(a.DateField) from Table1 as a Where a.DateField > Table1.DateField) as NextDate
From Table1

Write a new query, "qryDatesOver5", against "qryDates" to find where the two dates are more than 5 days apart - select the DateField and NextDate fields. These records have the borders of a greater than 5-day gap.

Write a 3rd query against qryDatesOver5 (similar to the first query but now you are starting with NextDate) to find the NextDate and its next closest DateField (could be the same as NextDate):

Select NextDate as StartGroup,
(Select Min(a.DateField) from qryDatesOver5 as a Where a.DateField >= qryDatesOver5.NextDate) as EndGroup
From qryDatesOver5

What you should have now is the start and end dates to use in grouping the records from the original table. The earliest group of dates won't be there (no NextDate exists for its StartGroup) but try out the above and we can come back to that problem.

 

[DanJR]

Here a query that assume the following table structure
[tt]
Table Name: tblDate
Field 1 : TheDate
Field 2 : Value
[/tt]

SELECT F.GroupDate,  Max(F.TheDate) AS MaxDate, Sum(F.[Value]) AS SumValue
FROM
(
SELECT  C.TheDate, G.GroupDate, C.[Value]
FROM (SELECT D2.CurrentDate AS GroupDate
       FROM (SELECT D.TheDate AS CurrentDate, 
                    (SELECT Max(D1.TheDate) 
                       FROM tblDate D1 
                       WHERE D1.TheDate<D.TheDate) AS PreviousDate
               FROM tblDate D) AS D2

       WHERE  (DateDiff('d',D2.PreviousDate,D2.CurrentDate) > 5 
               OR DateDiff('d',D2.PreviousDate,D2.CurrentDate) IS NULL) 
      ) AS G, 
     (SELECT TheDate, [Value] FROM tblDate) AS C

WHERE C.TheDate >= G.GroupDate
      AND (SELECT Count(*)
             FROM (SELECT D2.CurrentDate AS GroupDate
                     FROM (SELECT D.TheDate AS CurrentDate,  
                             (SELECT Max(D1.TheDate) 
                                FROM tblDate D1 
                                WHERE D1.TheDate<D.TheDate) AS PreviousDate
                             FROM tblDate D) AS D2

                      WHERE  (DateDiff('d',D2.PreviousDate,D2.CurrentDate) > 5 
                             OR DateDiff('d',D2.PreviousDate,D2.CurrentDate) IS NULL) 
                      ) AS Z
             WHERE C.TheDate >= Z.GroupDate AND Z.GroupDate >G.GroupDate
             ) = 0
) AS F
GROUP BY F.GroupDate

It gives the following output (not american date format):
[tt]
GroupDate MaxDate SumValue
1/1/04 4/1/04 3
10/1/04 19/1/04 6
25/1/04 4/2/04 4
[/tt]

Cheers,
Dan

 

[DanJR]

I made a typo in the output result. The sql result gives:
[tt]
GroupDate MaxDate SumValue
1/1/04 4/1/04 3
10/1/04 19/1/04 6
25/1/04 1/2/04 4
[/tt]

 

[raskew]

Dan -

Wasn't able to get your solution working.

Don't know if the intent is to do this only with a query, or
to arrive at the correct solution. If it's the latter, the
following code should do the trick.

I added GroupID (integer) to tblDate.

Public Sub GrpByGap(pGap As Integer)
'*******************************************
'Purpose:   Group dates with new group if
'           date exceeds preceeding date
'           by > pGap
're:        [URL unfurl="true"]http://www.tek-tips.com/viewthread.[/URL] _
                   cfm?SQID=846090&SPID=701&page=1
'Inputs:    from debug window:
'           call GrpByGap(5)
'Output:    Query1
'*******************************************

Dim db     As Database
Dim rs     As Recordset
Dim n      As Integer
Dim strSQL As String
Dim d1     As Date
Dim d2     As Date

Set db = CurrentDb

strSQL = "SELECT tblDate.theDate, tblDate.groupID" _
    & " FROM tblDate" _
    & " ORDER BY tblDate.theDate;"

Set rs = db.OpenRecordset(strSQL)
n = 1
d1 = rs!thedate

Do While Not rs.EOF
   With rs
      .Edit
      !groupID = n
      .Update
      .MoveNext
   End With
   If rs.EOF Then Exit Do
   d2 = rs!thedate
   n = IIf(d2 - d1 > pGap, n + 1, n)
   d1 = d2
Loop
rs.Close
db.Close
Set db = Nothing

DoCmd.OpenQuery "query1", acViewNormal, acReadOnly

End Sub

Query1 looks like this:

SELECT
    tblDate.groupID
  , Max(tblDate.theDate) AS MaxOftheDate
  , Sum(tblDate.value) AS SumOfvalue
FROM
   tblDate
GROUP BY
   tblDate.groupID;

Bob

 

[DanJR]

Hi Bob, I made the query in Access XP. Hmm, perhaps it doesn't support previous versions(?) eg using SELECT statements inside a FROM clause. It works ok for me. Which version of access did you try it on? Access 97?

Cheers,
Dan

 

[Bullsandbears123]

THank you, you all have been very helpful.