Merge several adjacent dates with SQL

Question

I am trying to merge several adjacent dates to get one single entry.

For example I have the following absences for one Employee:

02-Mai-17   01-Apr-18
02-Apr-18   01-Apr-19
02-Apr-19   01-Apr-20
02-Apr-20   30-Aug-20

The result should be: 02-Mai-17 30-Aug-20

I tried it with Combine consecutive date ranges to merge adjacent dates, but it works for my understanding only for one additional adjacent date.

I uploaded an example database under https://www.file-upload.net/download-13581528/Database1.accdb.html

SELECT IT2001.Id, IT2001.Kind, IT2001.Start, IT2001.End, 'Typ1'
FROM IT2001 LEFT JOIN IT2001 AS IT2001_1 ON (IT2001.Id = IT2001_1.Id) AND (IT2001.Kind = IT2001_1.Kind) AND (IT2001.Start-1=IT2001_1.End)
WHERE IT2001_1.Id IS NULL 
UNION ALL
SELECT IT2001.Id, IT2001.Kind, IT2001.Start, IT2001_1.End, 'Typ2'
FROM IT2001 INNER JOIN IT2001 AS IT2001_1 ON (IT2001.Id = IT2001_1.Id) AND (IT2001.Kind = IT2001_1.Kind) AND (IT2001_1.Start-1=IT2001.End)

and then:

SELECT Query1.Id, Query1.Kind, MIN(Query1.Start), Max(Query1.End)
FROM Query1
GROUP BY Query1.Id, Query1.Kind, Query1.Start

I am getting 3 rows instead of one:

02-Mai-17   01-Apr-19
02-Apr-18   01-Apr-20
02-Apr-19   30-Aug-20

How can I get one row or how could I delete the not required entries?

This is really painful in MS Access. Do you have any other database? Would you happen to have a calendar table or table of numbers? — Gordon Linoff, Apr 23 '19 at 20:28
Unfortunately I can only use MS Access, but I can add a new calendar table. I can I use the calendar table for the query which data should the new table content? — Paintitblack3k, Apr 23 '19 at 20:46
Yeh this works, but what if a gap occurs (minimum 1 day) between the dates? — Paintitblack3k, Apr 25 '19 at 21:28

score 0 · Answer 1 · answered Apr 24 '19 at 06:44

0

As all ranges combined leave no gap, this will do:

SELECT 
    Min(IT2001.Start) AS FirstDate, 
    Max(IT2001.End) AS LastDate
FROM 
    IT2001;

answered Apr 24 '19 at 06:44

Gustav

53,498
7
29
55

The difficulty is to check the gaps as well. I a gap occurs the dates can't be merged. Do you have an ideas to handle this? – Paintitblack3k Apr 25 '19 at 21:30
Your example data presents no gaps. That's all we have - which matches: _The result should be: 02-Mai-17 30-Aug-20_. – Gustav Apr 26 '19 at 06:13
You're absolutly right the described issue is solved :-) But you never which data you get and I suspose that I will get data with gaps as well. This seems much more complicated, but do you have an idea how to solve this cases? – Paintitblack3k Apr 26 '19 at 20:31
Not without the expected result. – Gustav Apr 27 '19 at 06:25

score 0 · Answer 2 · answered Apr 30 '19 at 18:50

I suppose I solved it with VBA and would like to invite you to comment or imporve it:

Sub mergeDates()
    Set db = CurrentDb

    i = 1
    Do While i > 0
        SQL = "UPDATE (IT2001 LEFT JOIN IT2001 AS IT2001_1 ON (IT2001.Start-1 = IT2001_1.End) AND (IT2001.Id = IT2001_1.Id) AND (IT2001.Kind = IT2001_1.Kind)) LEFT JOIN IT2001 AS IT2001_2 ON (IT2001.End = IT2001_2.Start-1) AND (IT2001.Kind = IT2001_2.Kind) AND (IT2001.Id = IT2001_2.Id)" & _
            " Set IT2001.End = IT2001_2.End, IT2001.combined=NZ(IT2001.combined,0)+1, IT2001_2.Delete = true" & _
            " Where IT2001_1.Start Is Null AND IT2001_2.End IS NOT NULL"
        db.Execute SQL

        delSQL = "DELETE * FROM IT2001 WHERE delete = true"
        db.Execute delSQL
        i = db.RecordsAffected

        j = j + 1
        If (j >= 10) Then
            i = 0
        End If
    Loop

End Sub

I check if the current entry is the first in the row (IT2001_1.Start Is Null) and
is if there an adjecent date (IT2001_2.End IS NOT NULL).
If both is true take over the second enddate into the first enddate (IT2001.End = IT2001_2.End) and mark the second entry to delete (IT2001_2.Delete = true)
I delete all entry where delete is true (DELETE * FROM IT2001 WHERE delete = true)

I put this in while loop to merge until I get 0 affected records (i = db.RecordsAffected)

Does anybody has any proposals or improvements?

Merge several adjacent dates with SQL

2 Answers2