Experts,

I've few sample records as :-

USE [Practice]
GO

CREATE TABLE #TEMP1
(
	ID		INT,
	CATG	CHAR(1),
	EFF_DT	DATETIME,
	TERM_DT	DATETIME
)

INSERT INTO #TEMP1 VALUES ( 2575, 'D', '2006-12-31 00:00:00.000', '2009-12-31 00:00:00.000' )
INSERT INTO #TEMP1 VALUES ( 2575, 'D', '2010-01-01 00:00:00.000', '2010-12-31 00:00:00.000' )
INSERT INTO #TEMP1 VALUES ( 2575, 'D', '2011-01-01 00:00:00.000', '2011-12-02 00:00:00.000' )

INSERT INTO #TEMP1 VALUES ( 2576, 'M', '2009-01-01 00:00:00.000', '2013-03-31 00:00:00.000' )
INSERT INTO #TEMP1 VALUES ( 2576, 'M', '2013-04-15 00:00:00.000', '2013-06-30 00:00:00.000' )

SELECT	ID, CATG, EFF_DT, TERM_DT
FROM	#TEMP1
ORDER BY ID, CATG, EFF_DT, TERM_DT

DROP TABLE #TEMP1

Question:-

;WITH cteGaps AS
(
	SELECT	ID, CATG, EFF_DT, TERM_DT, ROW_NUMBER() OVER ( PARTITION BY ID, CATG ORDER BY EFF_DT, TERM_DT ) AS RowNum
	FROM	#TEMP1
)

SELECT	a.*, '****' as delimiter, b.*
FROM	cteGaps a
		LEFT OUTER JOIN cteGaps b
		ON a.ID = b.ID
			AND a.CATG = b.CATG
			AND a.RowNum + 1 = b.RowNum 
ORDER BY a.ID, a.CATG, a.EFF_DT, a.TERM_DT

I'm getting my expected result with the help of above cte query, quick question is there any other approach to get it achieved with ONE single query instead of using cte?

Thanks in a

Your query absolutely looks good to me.

I may be changing the below if ID represents CATG:

1. Partition by as belos:

PARTITION BY ID ORDER BY EFF_DT, TERM_DT 

2. ORDER BY as below

ORDER BY a.ID ,a.RowNum asc

Hi, 

You can use a derived table.

SELECT A.ID, A.CATG, A.EFF_DT, A.TERM_DT,ROWNUMBER () OVER(PARTITION BY ID, CATG ORDER BY EFF_DT) AS ROWNUM
FROM #TEMP1 AS A

LEFT OUTER JOIN 

(SELECT ID, CATG, EFF_DT, TERM_DT, ROWNUMBER() OVER(PARTITION BY ID, CATG ORDER BYE BYE EFF_DT,TERM_DT ) AS ROWNUM
FROM #TEMP1) AS B
    ON A.ID=B.ID
    AND A.CATG=B.CATG
    AND A.ROWNUM+1=B.ROWNUM
ORDER BY A.ID, A.CATG, A.EFF_DT, A.TERM_DT

Hi gk1393,

For SQL Server 2012 and afterwards, a powerful operator LEAD is introduced for such scenario. You can reference the below which generate the same result as your CTE statement does.

SELECT ID,CATG,EFF_DT,TERM_DT,'****' as delimiter,
	  LEAD(ID,1) OVER ( PARTITION BY ID, CATG ORDER BY EFF_DT, TERM_DT ) AS ID2,
	  LEAD(CATG,1) OVER ( PARTITION BY ID, CATG ORDER BY EFF_DT, TERM_DT ) AS CATG2,
	  LEAD(EFF_DT,1) OVER ( PARTITION BY ID, CATG ORDER BY EFF_DT, TERM_DT ) AS EFF_DT2,
	  LEAD(TERM_DT,1) OVER ( PARTITION BY ID, CATG ORDER BY EFF_DT, TERM_DT ) AS TERM_DT2
FROM #TEMP1 AS a
ORDER BY a.ID, a.CATG, a.EFF_DT, a.TERM_DT

If you have any feedback on our support, please click here.