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)
-
Order by BP,EP asc– RoxyFeb 23, 2019 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.– RoxyFeb 23, 2019 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.– RoxyFeb 23, 2019 at 13:41
1 Answer
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 |