Merging records with consecutive dates

Question

I have the following problem in a Database using Access 2007 as front end and SQL2005 as back-end.

In Table A I have the following data structure

Table A

ID     Date            Supplier_ID   
1      10/22/2009      1              
2      10/23/2009      1             
3      10/24/2009      2   
4      10/25/2009      2           
5      10/26/2009      1

I need to merge values that have consecutive dates and the same Supplier_ID, increment a new column (Days) for each of these consecutive records and them write the data into Table B, so that I have

Table B

ID     Date            Supplier_ID      Days
1      10/22/2009      1                2
2      10/24/2009      2                2
3      10/26/2009      1                1

Only consecutive days should be merged. Hence ID 5 in table A has is added to table B as a new record. It's been some time since I've been working with Access VBA and wondered what the right approach to this would be.

Answer 1

I don't think this can be done in a single SQL, so you'll have to write some code. My first idea would be to make some kind of dictionary (Key = SupplierID, Value = (FirstDate, LastDate)) and use an algorithm that iterates through the data in Table A and fills the dictionary, kind of like this (pseudo-code):

records = recordset: SELECT * FROM Table A ORDER BY Date
Do Until records.EOF
   If dictionary contains records!SupplierID
       If dictionaryEntry.LastDate = records!Date - 1
           dictionaryEntry.LastDate = records!Date
       Else
           Make Table B Entry (Days = LastDate - FirstDate) and remove dictionary entry
       End If
   Else
       Create dictionary entry for this Supplier with LastDate = FirstDate = records!Date
   End If

   records.MoveNext
Loop
records.Close

Make Table B Entries for all remaining entries in the dictionary

For the dictionary, you could use a Scripting.Dictionary with the value being a 2-Element-Array.

Answer 2

Here's some ANSI-92 Query Mode syntax SQL DDL and DML to recreate the table and a possible solution:

CREATE TABLE TableA 
(
 ID INTEGER NOT NULL UNIQUE, 
 [Date] DATETIME NOT NULL, 
 Supplier_ID INTEGER NOT NULL
);

INSERT INTO TableA (ID, [Date], Supplier_ID) 
SELECT DT1.ID, DT1.[Date], DT1.Supplier_ID
FROM (
      SELECT DISTINCT 1 AS ID, '2009-10-22 00:00:00' AS [Date], 1 AS Supplier_ID FROM Customers
      UNION ALL 
      SELECT DISTINCT 2, '2009-10-23 00:00:00', 1 FROM Customers        
      UNION ALL 
      SELECT DISTINCT 3, '2009-10-24 00:00:00', 2 FROM Customers 
      UNION ALL 
      SELECT DISTINCT 4, '2009-10-25 00:00:00', 2 FROM Customers          
      UNION ALL 
      SELECT DISTINCT 5, '2009-10-26 00:00:00', 1  FROM Customers
     ) AS DT1;

CREATE VIEW TableA_StartDates (Supplier_ID, start_date)
AS
SELECT T1.Supplier_ID, T1.[Date] 
  FROM TableA AS T1
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableA AS T2
                    WHERE T2.Supplier_ID = T1.Supplier_ID 
                          AND DATEADD('D', -1, T1.[Date]) = T2.[Date]
                  );

CREATE VIEW TableA_EndDates (Supplier_ID, end_date)
AS
SELECT T3.Supplier_ID, T3.[Date] 
  FROM TableA AS T3
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM TableA AS T4
                    WHERE T4.Supplier_ID = T3.Supplier_ID 
                          AND DATEADD('D', 1, T3.[Date]) = T4.[Date]
                  );

CREATE VIEW TableA_Periods (Supplier_ID, start_date, end_date)
AS 
SELECT DISTINCT T5.Supplier_ID, 
       (
        SELECT MAX(S1.start_date)
          FROM TableA_StartDates AS S1
         WHERE S1.Supplier_ID = T5.Supplier_ID
               AND S1.start_date <= T5.[Date] 
       ),
       (
        SELECT MIN(E1.end_date)
          FROM TableA_EndDates AS E1
         WHERE E1.Supplier_ID = T5.Supplier_ID
               AND T5.[Date] <= E1.end_date
       )        
  FROM TableA AS T5;

SELECT P1.Supplier_ID, P1.start_date, P1.end_date, 
       DATEDIFF('D', P1.start_date, P1.end_date) + 1 AS Days
  FROM TableA_Periods AS P1;

Answer 3

You have a lot of business rules with some unique assumptions. Eg TableA is never empty, TableB is always empty before you run this

Regardless, the code below will work using your sample data:

Dim rs As Recordset
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb

Dim Supplier_ID As Integer
Dim Days As Integer
Dim FirstDate As Date
Set qdf = dbs.CreateQueryDef("", "select [Date], Supplier_ID from tableA order by [date] ")
Set rs = qdf.OpenRecordset()

Supplier_ID = rs!Supplier_ID
FirstDate = rs!Date
While Not rs.EOF
    If rs!Supplier_ID <> Supplier_ID Then
        If Supplier_ID <> 0 Then
            ' we don't want to insert the first time we run through this, so we check if Supplier_ID is not zero
            dbs.Execute ("insert into tableB ([Date], Supplier_ID, Days) select #" + Format(FirstDate, "dd-mmm-yyyy") + "#, " + Str(Supplier_ID) + ", " + Str(Days))
            Supplier_ID = rs!Supplier_ID
            FirstDate = rs!Date
            Days = 0
        End If

    End If
    Days = Days + 1
    rs.MoveNext
Wend

dbs.Execute ("insert into tableB ([Date], Supplier_ID, Days) select #" + Format(FirstDate, "dd-mmm-yyyy") + "#, " + Str(Supplier_ID) + ", " + Str(Days))

While you can do it, Access (and generally SQL) isn't the best for doing row by row comparisons. Even though you can use cursors or code like the example above, maintenance can be a challenge!