Query help with build new records based on dates

Kaumil Gandhi 26 Reputation points
2022-09-29T18:40:27.297+00:00

Hello All

Data set is here,

SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
  
UNION ALL  
  
SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
  
UNION ALL  
  
SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
UNION ALL  
SELECT '3' AS ID, CAST('01-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  

Expected o/p:-

SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual
UNION ALL
SELECT '1' AS ID, CAST('25-FEB-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'Out' AS C_Type FROM dual

UNION ALL

SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual

UNION ALL

SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual
UNION ALL
SELECT '3' AS ID, CAST('26-FEB-22' AS DATE) AS GRANT_DT, CAST('28-FEB-22' AS DATE) AS REVOKE_DT, 'Out' AS C_Type FROM dual
UNION ALL
SELECT '3' AS ID, CAST('01-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual

Adding new records with Out as C_Type since they not eligible for those days gaps

Thank Much

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.3K Reputation points
    2022-09-29T21:52:22.317+00:00

    I note that you are using FROM dual, which has a distinct flavour of Oracle over it. However, you are posting in a forum for Transact-SQL on SQL Server, so I will give you an SQL Server solution. If you want a solution that runs on Oracle, you need to find an Oracle forum.

       DECLARE @t TABLE (ID int, GRANT_DT date, REVOKE_DT date, C_Type varchar(20) )  
       INSERT @t  
            SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
            UNION ALL  
            SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
            UNION ALL  
            SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
            UNION ALL  
            SELECT '3' AS ID, CAST('01-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
         
       SELECT *FROM @t  
         
       ; WITH CTE AS (  
          SELECT *, next_grant = LEAD(GRANT_DT) OVER (PARTITION BY ID ORDER BY GRANT_DT)  
          FROM   @t  
       )  
       SELECT ID, dateadd(DAY, 1, REVOKE_DT) AS GRANT_DT,   
              isnull(dateadd(DAY, -1, next_grant), '20491231') AS REVOKE_DT, 'Out' AS C_Type  
       FROM   CTE  
       WHERE  datediff(DAY, REVOKE_DT, next_grant) > 1 OR  
              next_grant IS NULL AND REVOKE_DT < '20491231'   
       UNION ALL  
       SELECT ID, GRANT_DT, REVOKE_DT, C_Type  
       FROM   @t  
       ORDER  BY ID, GRANT_DT  
    
    0 comments No comments

  2. Kaumil Gandhi 26 Reputation points
    2022-09-30T01:56:25.453+00:00

    @Erland Sommarskog with your below query code

    ; WITH CTE AS (  
       SELECT *, LEAD(GRANT_DT) OVER (PARTITION BY ID ORDER BY GRANT_DT) AS next_grant  
       FROM   sample_data  
    )  
       
    --select * from CTE  
      
     SELECT ID, date_add(REVOKE_DT, 1) AS GRANT_DT,   
            --isnull(date_add(next_grant, 1), '2049-12-31') AS REVOKE_DT  
            date_add(next_grant, 1) AS REVOKE_DT  
            , 'Out' AS C_Type  
     FROM   CTE  
     WHERE  datediff(REVOKE_DT, next_grant) > 1 OR  
            next_grant IS NULL AND REVOKE_DT < '2049-12-31'  
      
    UNION ALL  
    SELECT ID, GRANT_DT, REVOKE_DT, C_Type  
    FROM   sample_data  
    ORDER  BY ID, GRANT_DT  
    

    But this record is NOT coming through:-

    SELECT '3' AS ID, CAST('26-FEB-22' AS DATE) AS GRANT_DT, CAST('28-FEB-22' AS DATE) AS REVOKE_DT, 'Out' AS C_Type FROM dual


  3. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2022-09-30T06:17:23.47+00:00

    Hi @Kaumil Gandhi
    Try this query:

    DECLARE @t TABLE (ID int, GRANT_DT date, REVOKE_DT date, C_Type varchar(20) )  
    INSERT @t  
    SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '3' AS ID, CAST('01-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
      
    ;WITH CTE AS  
    (  
     SELECT ID,C.Date_Value,C.Date_Type,C_Type  
           ,LEAD(Date_Value)OVER(PARTITION BY ID ORDER BY Date_Value) AS Next_Date  
     FROM @t CROSS APPLY(VALUES(GRANT_DT,'GRANT_DT'),(REVOKE_DT,'REVOKE_DT'))C(Date_Value,Date_Type)  
     WHERE Date_Value<>'31-DEC-49'  
    )  
    SELECT ID  
          ,CASE WHEN Date_Type='REVOKE_DT' AND DATEDIFF(DAY,Date_Value,Next_Date)>1 THEN DATEADD(DAY,1,Date_Value)   
    	        ELSE Date_Value END AS GRANT_DT  
    	  ,CASE WHEN Next_Date IS NULL THEN '31-DEC-49'    
    	        WHEN Date_Type='REVOKE_DT' AND DATEDIFF(DAY,Date_Value,Next_Date)>1 THEN DATEADD(DAY,-1,Next_Date)  
    	        ELSE Next_Date END AS REVOKE_DT  
    	  ,CASE WHEN Date_Type='REVOKE_DT' AND Next_Date IS NULL THEN 'Out'   
    	        WHEN Date_Type='REVOKE_DT' AND DATEDIFF(DAY,Date_Value,Next_Date)>1 THEN 'Out'  
    	        ELSE C_Type END AS C_Type  
    FROM CTE  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  4. Kaumil Gandhi 26 Reputation points
    2022-10-04T02:59:35.827+00:00

    Hello @CosmogHong-MSFT

    Sorry my expected o/p is here:-

     SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('24-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
     UNION ALL  
     SELECT '1' AS ID, CAST('25-FEB-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'Out' AS C_Type FROM dual  
          
     UNION ALL  
          
     SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
          
     UNION ALL  
          
     SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('24-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
     UNION ALL  
     SELECT '3' AS ID, CAST('25-FEB-22' AS DATE) AS GRANT_DT, CAST('01-MAR-22' AS DATE) AS REVOKE_DT, 'Out' AS C_Type FROM dual  
     UNION ALL  
     SELECT '3' AS ID, CAST('02-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type FROM dual  
    
    0 comments No comments

  5. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2022-10-04T07:17:31.873+00:00

    Hi @Kaumil Gandhi
    Check this:

    DECLARE @t TABLE (ID int, GRANT_DT date, REVOKE_DT date, C_Type varchar(20) )  
    INSERT @t  
    SELECT '1' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '2' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '3' AS ID, CAST('01-JAN-22' AS DATE) AS GRANT_DT, CAST('25-FEB-22' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
    UNION ALL  
    SELECT '3' AS ID, CAST('01-MAR-22' AS DATE) AS GRANT_DT, CAST('31-DEC-49' AS DATE) AS REVOKE_DT, 'In' AS C_Type  
          
    ;WITH CTE AS  
    (  
     SELECT ID,C.Date_Value,C.Date_Type,C_Type  
           ,LEAD(Date_Value)OVER(PARTITION BY ID ORDER BY Date_Value) AS Next_Date  
    	,LAG(Date_Value)OVER(PARTITION BY ID ORDER BY Date_Value) AS Last_Date  
     FROM @t CROSS APPLY(VALUES(GRANT_DT,'GRANT_DT'),(REVOKE_DT,'REVOKE_DT'))C(Date_Value,Date_Type)  
     WHERE Date_Value<>'31-DEC-49'  
    )  
    SELECT ID  
          ,CASE WHEN Date_Type='GRANT_DT' AND DATEDIFF(DAY,Last_Date,Date_Value)>1 THEN DATEADD(DAY,1,Date_Value)  
                ELSE Date_Value END AS GRANT_DT  
          ,CASE WHEN Next_Date IS NULL THEN '31-DEC-49'    
                WHEN Date_Type='REVOKE_DT' AND DATEDIFF(DAY,Date_Value,Next_Date)>1 THEN Next_Date  
                ELSE DATEADD(DAY,-1,Next_Date) END AS REVOKE_DT  
          ,CASE WHEN Date_Type='REVOKE_DT' AND Next_Date IS NULL THEN 'Out'   
                WHEN Date_Type='REVOKE_DT' AND DATEDIFF(DAY,Date_Value,Next_Date)>1 THEN 'Out'  
                ELSE C_Type END AS C_Type  
    FROM CTE  
    

    Output:
    247306-image.png

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments