CODEWITHSUNDEEP
sqlsql-server
devDalida1996
devDalida1996

Reputation: 87

Merge two records date if ToDate equal second FromDate SQL Server?

Example data:

FK_EmployeeId   FromDate                  ToDate                      DateDiff
20325           2016-06-24 00:00:00.000   2016-06-25 00:00:00.000     2
20325           2016-06-25 00:00:00.000   2016-06-26 00:00:00.000     2
20325           2016-06-26 00:00:00.000   2016-06-28 00:00:00.000     3
20325           2016-06-28 00:00:00.000   2016-06-29 00:00:00.000     2
20325           2016-06-29 00:00:00.000   2016-06-30 00:00:00.000     2
20325           2016-06-30 00:00:00.000   2016-07-01 00:00:00.000     2
20325           2016-07-01 00:00:00.000   2016-07-02 00:00:00.000     2
20325           2016-07-02 00:00:00.000   2016-07-03 00:00:00.000     2
20325           2016-07-03 00:00:00.000   2016-07-04 00:00:00.000     2
20325           2016-07-04 00:00:00.000   2016-07-05 00:00:00.000     2

And I would like to get the following output:

FK_EmployeeId   FromDate                  ToDate                      DateDiff
20325           2016-06-24 00:00:00.000   2016-06-26 00:00:00.000     3
20325           2016-06-28 00:00:00.000   2016-07-05 00:00:00.000     8

Upvotes: 0

Views: 44

Answers (1)

Paul Andrew
Paul Andrew

Reputation: 3253

I think you are trying to do something like the below using a windowing function to join the records to themselves to perform the date comparisons across 2 rows.

However as people have already suggested in the comments your sample data above contains all sequential dates so its not really clear how your expecting this to work. I've therefore excluded a middle value in the sample data to demonstrate the approach.

--TABLE JUST FOR EXAMPLE QUERY
DECLARE @SomeTable TABLE ([FK_EmployeeId] INT,[FromDate] DATETIME,[ToDate] DATETIME,[DateDiff] INT)

--YOUR SAMPLE DATA
INSERT INTO @SomeTable 
    ([FK_EmployeeId],[FromDate],[ToDate],[DateDiff])
SELECT 20325,'2016-06-24 00:00:00.000','2016-06-25 00:00:00.000',2
UNION SELECT 20325,'2016-06-25 00:00:00.000','2016-06-26 00:00:00.000',2
UNION SELECT 20325,'2016-06-26 00:00:00.000','2016-06-28 00:00:00.000',3
UNION SELECT 20325,'2016-06-28 00:00:00.000','2016-06-29 00:00:00.000',2
UNION SELECT 20325,'2016-06-29 00:00:00.000','2016-06-30 00:00:00.000',2
--UNION SELECT 20325,'2016-06-30 00:00:00.000','2016-07-01 00:00:00.000',2 --<<
UNION SELECT 20325,'2016-07-01 00:00:00.000','2016-07-02 00:00:00.000',2
UNION SELECT 20325,'2016-07-02 00:00:00.000','2016-07-03 00:00:00.000',2
UNION SELECT 20325,'2016-07-03 00:00:00.000','2016-07-04 00:00:00.000',2
UNION SELECT 20325,'2016-07-04 00:00:00.000','2016-07-05 00:00:00.000',2

--THE POINT:
;WITH cte AS
    (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [FK_EmployeeId] ORDER BY [ToDate]) AS 'RowNo'
    FROM 
        @SomeTable
    )

SELECT
    a.*,
    DATEDIFF(DAY,a.[FromDate],b.[ToDate]) AS 'NewDiff'
FROM
    cte a
    JOIN cte b
        ON a.[RowNo] + 1 = b.[RowNo]
WHERE
    a.[ToDate] <> b.[FromDate]

A little more detail in the question next time please.

Upvotes: 1

Related Questions