Database

0x01AA wrote:

The query itself returns the result in about 0.03 seconds which is pretty much nice I think.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

0x01AA wrote:

Using a temp table, then it takes nearly 0 seconds, but temp table with Interbase is very uncomfortable compared to MSSQL.

Bastard Programmer from Hell
If you can't read my code, try converting it here[^]

DECLARE @StartDate datetime2(0) = '2016-01-04 02:19:45.000';
DECLARE @EndDate datetime2(0) = '2016-08-31 07:57:37.000';
DECLARE @EndDay date = CAST(@EndDate As date);

WITH CW (StartDate, EndDate) As
(
    SELECT
        @StartDate,
        DateAdd(wk, 1, CAST(CAST(@StartDate As date) As datetime2(0)))

    UNION ALL

    SELECT
        EndDate,
        CASE
            WHEN DateAdd(wk, 1, EndDate) < @EndDay THEN DateAdd(wk, 1, EndDate)
            ELSE @EndDate
        END
    FROM
        CW
    WHERE
        EndDate < @EndDay
)
SELECT
    StartDate,
    EndDate
FROM 
    CW
;

StartDate               EndDate
-------------------     -------------------
2016-01-04 02:19:45     2016-01-11 00:00:00
2016-01-11 00:00:00     2016-01-18 00:00:00
...
2016-08-22 00:00:00     2016-08-29 00:00:00
2016-08-29 00:00:00     2016-08-31 07:57:37

MachineID    ProcessID    StartDate              EndDate
---------    ---------    ---------              -------
50           1            2016-01-04 02:19:45    2016-06-08 07:57:37
50           1            2016-10-01 02:19:45    2016-12-31 07:57:37

WITH CW (ProcessID, StartDate, EndDate, RunEnd, RunEndDay) As
(
    SELECT
        ProcessID,
        StartDate,
        DateAdd(wk, 1, CAST(CAST(StartDate As date) As datetime2(0))),
        EndDate,
        CAST(EndDate As date)
    FROM
        YourTable
    WHERE
        MachineID = 50
 
    UNION ALL
 
    SELECT
        ProcessID,
        EndDate,
        CASE
            WHEN DateAdd(wk, 1, EndDate) < RunEndDay THEN DateAdd(wk, 1, EndDate)
            ELSE RunEnd
        END,
        RunEnd,
        RunEndDay
    FROM
        CW
    WHERE
        EndDate < RunEndDay
)
SELECT
    ProcessID,
    StartDate,
    EndDate
FROM 
    CW
ORDER BY
    ProcessID,
    StartDate
;

ProcessID    StartDate              EndDate
---------    ---------              -------
1            2016-01-04 02:19:45    2016-01-11 00:00:00
1            2016-01-11 00:00:00    2016-01-18 00:00:00
...
1            2016-05-30 00:00:00    2016-06-06 00:00:00
1            2016-06-06 00:00:00    2016-06-08 07:57:37

2            2016-10-01 02:19:45    2016-10-08 00:00:00
2            2016-10-08 00:00:00    2016-10-15 00:00:00
...
2            2016-12-17 00:00:00    2016-12-24 00:00:00
2            2016-12-24 00:00:00    2016-12-31 07:57:37

var ds = new DataSet();

using (var connection = new SqlConnection("..."))
{
    var da = new SqlDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "FirstTable");
}

using (var connection = new OleDbConnection("..."))
{
    var da = new OleDbDataAdapter("SELECT ...", connection);
    da.SelectCommand.Parameters.AddWithValue(...);
    da.Fill(ds, "SecondTable");
}