SQL Server Database flatten records

Question

Sorry for the confusion. I have below attached table sql server database table holding continuous and non continuous records. When i say continuous that means EP of first record if Beginning Point(BP). I need to write a query to transform such records into one record like shown in the target. Please help me build a query that can help me solve the purpose. (SQL Server 2014)

Answer 1

This appears to be a 'GAPS' and 'ISLAND' problem. The following example works against your test data.

--demo setup
Declare @Table1 table (BP decimal(5,1), EP decimal (5,1), Latest_Date date, [Order] varchar(10))
insert into @table1(BP,EP,Latest_Date,[Order]) values
(136,138.5,'2/15/2019','UMT004'),
(138.5,143.5,'2/21/2019','UMT004'),
(143.5,144.8,'2/15/2019','UMT004'),
(150,160.5,'2/17/2019','UMT004'),
(170,190.3,'2/18/2019','UMT004')

--the solution
;WITH CTE_DataIslands -- First CTE determine the start of each new data island
AS (
    SELECT *
        ,(
            CASE 
                WHEN (
                        LAG(EP, 1, - 1) OVER (
                            PARTITION BY [Order] ORDER BY BP
                                ,EP ASC
                            )
                        ) <> (BP)
                    THEN 1
                ELSE 0
                END
            ) AS [IsNewDataIsland]
    FROM @Table1
    )
    ,CTE_GenerateGroupingID
AS (
    SELECT BP
        ,EP
        ,Latest_Date
        ,[order]
        ,SUM([IsNewDataIsland]) OVER (
            PARTITION BY [ORDER] ORDER BY bp
                ,ep ROWS UNBOUNDED PRECEDING
            ) AS GroupingID -- Create a running total of the IsNewDataIsland column this will create a grouping id we can now group on
    FROM CTE_DataIslands
    )
SELECT MIN(BP) AS [BP]
    ,MAX([EP]) AS [BP]
    ,MAX(Latest_Date) AS Latest_Date
    ,MIN([order]) AS [Order]
FROM CTE_GenerateGroupingID
GROUP BY GroupingID

---

| BP    | BP    | Latest_Date | Order  |
|-------|-------|-------------|--------|
| 136.0 | 144.8 | 2019-02-21  | UMT004 |
| 150.0 | 160.5 | 2019-02-17  | UMT004 |
| 170.0 | 190.3 | 2019-02-18  | UMT004 |