Datediff with parameters in one table

Juan Lopez 21 Reputation points
2021-02-01T14:25:15.193+00:00

I have the following table, what I want to achieve is to count the number of days based on the disconnection and reconnection. The list of accounts will be displayed if the reconnection date is a year or more since the disconnection date as displayed on the second table. Thank you in advance for those who can help.

62527-image.png

62577-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,879 questions
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

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2021-02-01T15:31:01.773+00:00
    DECLARE @T TABLE (
     Id int IDENTITY,
     AccountNumber int,
     Partculars varchar(20),
     TansactionDate date
    );
    
    INSERT INTO @T VALUES
    (3001, 'DISCONNECTED', '03/09/2013'),
    (3002, 'DISCONNECTED', '04/29/2014'),
    (3002, 'RECONNECTED', '10/23/2014'),
    (3001, 'RECONNECTED', '11/28/2015'),
    (3001, 'DISCONNECTED', '02/13/2016'),
    (3003, 'DISCONNECTED', '07/22/2017'),
    (3003, 'RECONNECTED', '07/25/2017'),
    (3001, 'RECONNECTED', '09/01/2017'),
    (3004, 'DISCONNECTED', '08/20/2018'),
    (3004, 'RECONNECTED', '08/31/2019'),
    (3005, 'DISCONNECTED', '10/08/2019');
    
    ;WITH CTE_DISCONNECTED AS (
     SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY Id) AS RN
     FROM @T WHERE Partculars = 'DISCONNECTED'
    ),
    CTE_RECONNECTED AS (
     SELECT *, ROW_NUMBER() OVER(PARTITION BY AccountNumber ORDER BY Id) AS RN 
     FROM @T WHERE Partculars = 'RECONNECTED'
    )
    
    SELECT DISTINCT r.AccountNumber
    FROM CTE_RECONNECTED AS r
    INNER JOIN CTE_DISCONNECTED AS d ON d.AccountNumber = r.AccountNumber
    AND r.RN = d.RN
    AND DATEDIFF(DAY, d.TansactionDate, r.TansactionDate) >= 365;
    
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2021-02-01T16:11:25.493+00:00

    Another way

    DECLARE @T TABLE (  
        Id int IDENTITY,  
        AccountNumber int,  
        Partculars varchar(20),  
        TransactionDate date  
    );  
         
    INSERT INTO @T VALUES  
    (3001, 'DISCONNECTED', '03/09/2013'),  
    (3002, 'DISCONNECTED', '04/29/2014'),  
    (3002, 'RECONNECTED', '10/23/2014'),  
    (3001, 'RECONNECTED', '11/28/2015'),  
    (3001, 'DISCONNECTED', '02/13/2016'),  
    (3003, 'DISCONNECTED', '07/22/2017'),  
    (3003, 'RECONNECTED', '07/25/2017'),  
    (3001, 'RECONNECTED', '09/01/2017'),  
    (3004, 'DISCONNECTED', '08/20/2018'),  
    (3004, 'RECONNECTED', '08/31/2019'),  
    (3005, 'DISCONNECTED', '10/08/2019'),  
    (3006, 'DISCONNECTED', '12/31/2019'),  
    (3006, 'RECONNECTED', '01/01/2020');  
         
    ;With cte As  
    (Select AccountNumber, Partculars As TrailingParticulars, TransactionDate As TrailingDate,  
      Lead(Partculars) Over(Partition By AccountNumber Order By TransactionDate) As LeadingParticulars,  
      Lead(TransactionDate) Over(Partition By AccountNumber Order By TransactionDate) As LeadingDate  
    From @T)  
    Select Distinct AccountNumber  
    From cte  
    Where TrailingParticulars = 'DISCONNECTED' And LeadingParticulars = 'RECONNECTED'  
      And DateAdd(year, 1, TrailingDate) <= LeadingDate  
    Order By AccountNumber;  
    

    Tom

    1 person found this answer helpful.
    0 comments No comments

  2. EchoLiu-MSFT 14,571 Reputation points
    2021-02-02T03:22:20.66+00:00

    Hi @Juan Lopez ,

    Please refer to:

         DECLARE @Table TABLE (  
          Id int IDENTITY,  
          AccountNumber int,  
          Partculars varchar(20),  
          TansactionDate date  
         );  
              
         INSERT INTO @Table VALUES  
         (3001, 'DISCONNECTED', '03/09/2013'),  
         (3002, 'DISCONNECTED', '04/29/2014'),  
         (3002, 'RECONNECTED', '10/23/2014'),  
         (3001, 'RECONNECTED', '11/28/2015'),  
         (3001, 'DISCONNECTED', '02/13/2016'),  
         (3003, 'DISCONNECTED', '07/22/2017'),  
         (3003, 'RECONNECTED', '07/25/2017'),  
         (3001, 'RECONNECTED', '09/01/2017'),  
         (3004, 'DISCONNECTED', '08/20/2018'),  
         (3004, 'RECONNECTED', '08/31/2019'),  
         (3005, 'DISCONNECTED', '10/08/2019');  
           
        ;WITH cte  
        as(SELECT *,LEAD(TansactionDate,1) OVER(partition by AccountNumber ORDER BY TansactionDate) LEADTansactionDate FROM @Table)  
          
        SELECT DISTINCT AccountNumber FROM cte  
        WHERE DATEDIFF(DAY,TansactionDate,LEADTansactionDate)>365  
    

    Output:
    62783-image.png

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    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