CODEWITHSUNDEEP
sqldatems-accessdays
Dofin
Dofin

Reputation: 5

Sql query to split the rows according to date range one day using sql query in Ms access

Example

Existing Table

Id name col3  ... Startdate EndDate   days

1 ABC value... 01-jan-2020 03-Jan-2020 3

2 AB value... 01-feb-2020 10-feb-2020 5

3 AC value... 02-feb-2020 02-feb-2020 1

Required result

Id name col3 ... Startdate EndDate  days

1 ABC value ...01-jan-2020 01-jan-2020 1

1 ABC value ...02-jan-2020 02-jan-2020 1

1 ABC value ...03-jan-2020 03-jan-2020 1

2 AB value... 01-feb-2020 01-feb-2020 1

2 AB value... 02-feb-2020 02-feb-2020 1

2 AB value... 03-feb-2020 03-feb-2020 1

2 AB value... 04-feb-2020 04-feb-2020 1

2 AB value... 05-feb-2020 05-feb-2020 1

3 AC value... 02-feb-2020 02-feb-2020 1

I need a sql query to keep all columns unchanged, and update the dates in a interval of one day, Split the dates and place one in days column. If we have only one then no change if there are more days then we have to split the dates and put one in days column as shown above.

Upvotes: 0

Views: 219

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can do this in MS Access if you have a tally table of some sort. This is a table that simply contains numbers. You can create one by inserting rows into a table that only has an identity column.

With such a table, you could use:

select id, name, . . . 
       dateadd("d", n.n - 1, startdate) as startdate,
       dateadd("d", n.n - 1, enddate) as enddate,
       1 as days
from t inner join
     tally as n
     on n.n <= t.days;

Note: MS Access makes it really hard to create a tally table within a query. This is somewhere between easy and trivial in other databases.

Upvotes: 0

Gustav
Gustav

Reputation: 55806

@June7 is right. VBA is the right tool for this:

Public Function ExpandTable()

    Dim Records     As DAO.Recordset
    
    Dim ThisId      As Long
    Dim ThisName    As String
    Dim ThisCol3    As String
    Dim EndDate     As Date
    Dim NextDate    As Date
    
    Set Records = CurrentDb.OpenRecordset("Select * From TableTest Order By Id, StartDate")
    
    Records.MoveFirst
    While Not Records.EOF
        If ThisId <> Records!Id.Value Then
            ThisId = Records!Id.Value
            NextDate = Records!StartDate.Value
            EndDate = Records!EndDate.Value
            If NextDate < EndDate Then
                Records.Edit
                    Records!EndDate.Value = NextDate
                Records.Update
            End If
        End If
        While NextDate < EndDate
            ThisName = Records!Name.Value
            ThisCol3 = Records!Col3.Value
            NextDate = DateAdd("d", 1, NextDate)
            Records.AddNew
                Records!Id.Value = ThisId
                Records!Name.Value = ThisName
                Records!Col3.Value = ThisCol3
                Records!StartDate.Value = NextDate
                Records!EndDate.Value = NextDate
            Records.Update
        Wend
        Records.MoveNext
    Wend
    Records.Close
    
End Function

Result:

enter image description here

Upvotes: 1

Related Questions