locked
Query question RRS feed

  • 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', '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 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