SQL Server : merge 2 rows into 1 with condition

Question

I have a query that returns this results

-----------------------------------------------------------------
|ID |ID1|ID2|IDP|IDC|IDA|START_DATE         |END_DATE           |
-----------------------------------------------------------------
|10 |2  |1  |10 |152|15 |1900-01-01 08:10:00|1900-01-01 09:10:00|
|10 |2  |1  |20 |152|12 |1900-01-01 09:10:00|1900-01-01 10:10:00|
|10 |2  |1  |30 |152|11 |1900-01-01 10:10:00|1900-01-01 11:10:00|
|10 |2  |1  |10 |152|15 |1900-01-01 11:10:00|1900-01-01 12:10:00|
|10 |2  |1  |10 |152|15 |1900-01-01 12:10:00|1900-01-01 13:10:00|
-----------------------------------------------------------------

I need to merge the last 2 rows because the END_DATE of the fourth row is equal to the START_DATE of fifth row. The other rows are all right and they have to be like that. So I would like to have this result.

-----------------------------------------------------------------
|ID |ID1|ID2|IDP|IDC|IDA|START_DATE         |END_DATE           |
-----------------------------------------------------------------
|10 |2  |1  |10 |152|15 |1900-01-01 08:10:00|1900-01-01 09:10:00|
|10 |2  |1  |20 |152|12 |1900-01-01 09:10:00|1900-01-01 10:10:00|
|10 |2  |1  |30 |152|11 |1900-01-01 10:10:00|1900-01-01 11:10:00|
|10 |2  |1  |10 |152|15 |1900-01-01 11:10:00|1900-01-01 13:10:00|
-----------------------------------------------------------------

With the fourth and fifth rows

-----------------------------------------------------------------
|10 |2  |1  |10 |152|15 |1900-01-01 11:10:00|1900-01-01 12:10:00|
|10 |2  |1  |10 |152|15 |1900-01-01 12:10:00|1900-01-01 13:10:00|
-----------------------------------------------------------------

They have to became only one

-----------------------------------------------------------------
|10 |2  |1  |10 |152|15 |1900-01-01 11:10:00|1900-01-01 13:10:00|
-----------------------------------------------------------------

How can I do that?

See also answers for similar question: http://stackoverflow.com/questions/6068619/merging-date-intervals-in-sql-server — limit, Mar 11 '16 at 20:30

score 0 · Answer 1 · answered Mar 11 '16 at 08:26

0

Presuming that your output query is a table, than this might works:

(@tmp - table with your return result)

select   t.id,
         t.id1,
         t.id2,
         t.idp,          t.idc,
         t.ida,
         t.startDate,
         t2.enddate  from @tmp t left outer join @tmp t2 on t2.startDate = t.enddate where t2.id is not null

But as Joe Taras said, please add what you have written or the table where the data are stored.

answered Mar 11 '16 at 08:26

JassyJov

204
1
9

That query merges my fourth row and fifth row but compromizes the START_DATE and END_DATE of the other records Example of the first row Before START DATE 1900-01-01 09:10:00 After 1900-01-01 10:10:00 – Bruno Mar 11 '16 at 09:23

score 0 · Accepted Answer · answered Mar 11 '16 at 10:18

In SQL Server 2012+ it can be done much more efficiently with LAG function.

In SQL Server 2008 I'd use cursor if the amount of data is significant.

The following set-based approach works, but it is likely to be slow with large table.

Sample data

DECLARE @T TABLE 
    (ID int, ID1 int, ID2 int, IDP int, IDC int, IDA int, 
    START_DATE datetime2(0), END_DATE datetime2(0));

INSERT INTO @T
    (ID, ID1, ID2, IDP, IDC, IDA, START_DATE, END_DATE)
VALUES
    (10, 2, 1, 10, 152, 15, '1900-01-01 08:10:00', '1900-01-01 09:10:00'),
    (10, 2, 1, 20, 152, 12, '1900-01-01 09:10:00', '1900-01-01 10:10:00'),
    (10, 2, 1, 30, 152, 11, '1900-01-01 10:10:00', '1900-01-01 11:10:00'),
    (10, 2, 1, 10, 152, 15, '1900-01-01 11:10:00', '1900-01-01 12:10:00'),
    (10, 2, 1, 10, 152, 15, '1900-01-01 12:10:00', '1900-01-01 13:10:00');

Query

CTE_Merged joins the table with itself on all ID columns and start+end dates. It will produce one row for each pair of rows that should be merged.

CTE_Join joins original table with CTE_Merged twice. First - to get the FinalEndDate for the rows that should be preserved; second - to filter out second row of the merged pair (using WHERE CTE_Join.M2END_DATE IS NULL).

Run this query step by step and examine intermediate results to understand how it works.

WITH
CTE_Merged
AS
(
    SELECT
        T1.ID
        ,T1.ID1
        ,T1.ID2
        ,T1.IDP
        ,T1.IDC
        ,T1.IDA
        ,T1.START_DATE
        ,T2.END_DATE AS MergedEndDate
    FROM
        @T AS T1
        INNER JOIN @T AS T2
            ON  T1.ID  = T2.ID
            AND T1.ID1 = T2.ID1
            AND T1.ID2 = T2.ID2
            AND T1.IDP = T2.IDP
            AND T1.IDC = T2.IDC
            AND T1.IDA = T2.IDA
            AND T1.END_DATE = T2.START_DATE
)
,CTE_Join
AS
(
    SELECT
        T.ID
        ,T.ID1
        ,T.ID2
        ,T.IDP
        ,T.IDC
        ,T.IDA
        ,T.START_DATE
        ,T.END_DATE
        ,M1.START_DATE AS M1START_DATE
        ,M1.MergedEndDate AS M1END_DATE
        ,ISNULL(M1.MergedEndDate, T.END_DATE) AS FinalEndDate
        ,M2.START_DATE AS M2START_DATE
        ,M2.MergedEndDate AS M2END_DATE
    FROM
        @T AS T
        LEFT JOIN CTE_Merged AS M1
            ON  T.ID  = M1.ID
            AND T.ID1 = M1.ID1
            AND T.ID2 = M1.ID2
            AND T.IDP = M1.IDP
            AND T.IDC = M1.IDC
            AND T.IDA = M1.IDA
            AND T.START_DATE = M1.START_DATE
        LEFT JOIN CTE_Merged AS M2
            ON  T.ID  = M2.ID
            AND T.ID1 = M2.ID1
            AND T.ID2 = M2.ID2
            AND T.IDP = M2.IDP
            AND T.IDC = M2.IDC
            AND T.IDA = M2.IDA
            AND T.END_DATE = M2.MergedEndDate
)
SELECT
    CTE_Join.ID
    ,CTE_Join.ID1
    ,CTE_Join.ID2
    ,CTE_Join.IDP
    ,CTE_Join.IDC
    ,CTE_Join.IDA
    ,CTE_Join.START_DATE
    ,CTE_Join.FinalEndDate
FROM CTE_Join
WHERE
    CTE_Join.M2END_DATE IS NULL
;

Result

+----+-----+-----+-----+-----+-----+---------------------+---------------------+
| ID | ID1 | ID2 | IDP | IDC | IDA |     START_DATE      |    FinalEndDate     |
+----+-----+-----+-----+-----+-----+---------------------+---------------------+
| 10 |   2 |   1 |  10 | 152 |  15 | 1900-01-01 08:10:00 | 1900-01-01 09:10:00 |
| 10 |   2 |   1 |  20 | 152 |  12 | 1900-01-01 09:10:00 | 1900-01-01 10:10:00 |
| 10 |   2 |   1 |  30 | 152 |  11 | 1900-01-01 10:10:00 | 1900-01-01 11:10:00 |
| 10 |   2 |   1 |  10 | 152 |  15 | 1900-01-01 11:10:00 | 1900-01-01 13:10:00 |
+----+-----+-----+-----+-----+-----+---------------------+---------------------+

That is PERFECT! Thank you! ;) – Bruno Mar 11 '16 at 11:11 — Bruno, Mar 11 '16 at 11:11

SQL Server : merge 2 rows into 1 with condition

2 Answers2