-2

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)

flatten records for continuous data

|improve this question
  • Order by BP,EP asc – Roxy Feb 23 '19 at 13:28
  • If i just group by order to get min(bp), max(ep), max(date) then there will be other records which are not continuous in the same order will be impacted. I need to flatten only continuous records. – Roxy Feb 23 '19 at 13:38
  • Query has to scan through each and every row to validate if the records are continuous or not continuous based on current record(EP)=LEAD(BP) to flatten and rest of the records that doesnt fit into this condition have to be selected as is. – Roxy Feb 23 '19 at 13:41
2

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 |
|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.