Answered by:
Query question
Question

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', '20061231 00:00:00.000', '20091231 00:00:00.000' ) INSERT INTO #TEMP1 VALUES ( 2575, 'D', '20100101 00:00:00.000', '20101231 00:00:00.000' ) INSERT INTO #TEMP1 VALUES ( 2575, 'D', '20110101 00:00:00.000', '20111202 00:00:00.000' ) INSERT INTO #TEMP1 VALUES ( 2576, 'M', '20090101 00:00:00.000', '20130331 00:00:00.000' ) INSERT INTO #TEMP1 VALUES ( 2576, 'M', '20130415 00:00:00.000', '20130630 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 advance
Please do let us know your feedback. Thank You  KG, MCTS
Monday, February 16, 2015 6:33 AM
Answers

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
Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered
 Marked as answer by Eric__Zhang Wednesday, February 25, 2015 6:16 AM
Monday, February 16, 2015 6:49 AM 
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.
Eric Zhang
TechNet Community Support
 Edited by Eric__Zhang Monday, February 16, 2015 8:35 AM
 Marked as answer by Eric__Zhang Wednesday, February 25, 2015 6:16 AM
Monday, February 16, 2015 8:35 AM
All replies

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
Monday, February 16, 2015 6:46 AM 
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
Please Vote as Helpful if an answer is helpful and/or Please mark Proposed as Answer or Mark As Answer when question is answered
 Marked as answer by Eric__Zhang Wednesday, February 25, 2015 6:16 AM
Monday, February 16, 2015 6:49 AM 
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.
Eric Zhang
TechNet Community Support
 Edited by Eric__Zhang Monday, February 16, 2015 8:35 AM
 Marked as answer by Eric__Zhang Wednesday, February 25, 2015 6:16 AM
Monday, February 16, 2015 8:35 AM