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,552 questions
0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    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
  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 22,941 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
  5. CosmogHong-MSFT 22,941 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