Datediff with parameters in one table

Question

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.

Answer 1

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;

Answer 2

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

Answer 3

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:

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.