SQL Code to have same date for multiple rows

Question

My data is structured as below -

1.For each ID month denotes reporting month, Sub created is the original subscription purchase date, status = whether customer was active or not, tenure is lifetime months ( It resets to 1 upon the customer returning )

ID Month Sub_created status tenure
100 2017-02-01 2017-02-01 active 1
100 2017-03-01 active 2
100 2017-04-01 active 3
100 2017-05-01 churned 3
100 2021-02-01 2021-02-01 active 1
100 2021-03-01 active 2
100 2021-04-01 active 3
100 2021-05-01 active 4
100 2021-06-01 active 5
100 2021-07-01 active 6

I want to be able to have sub created date for all the rows till it has a new subscription date. The output I am trying to get is below -

ID Month Sub_created status tenure
100 2017-02-01 2017-02-01 active 1
100 2017-03-01 2017-02-01 active 2
100 2017-04-01 2017-02-01 active 3
100 2017-05-01 2017-02-01 churned 3
100 2021-02-01 2021-02-01 active 1
100 2021-03-01 2021-02-01 active 2
100 2021-04-01 2021-02-01 active 3
100 2021-05-01 2021-02-01 active 4
100 2021-06-01 2021-02-01 active 5
100 2021-07-01 2021-02-01 active 6
Can anyone suggest SQL code ? Thanks

Answer 1

Hi @ajax6785 ，

Welcome to Microsoft Q&A!

Please also refer below:

create table tableaj  
(ID int,  
[Month] date,  
Sub_created date,  
status varchar(10),  
tenure int)  
  
insert into tableaj values  
(100,'2017-02-01', '2017-02-01', 'active', 1),  
(100 ,'2017-03-01', NULL,'active', 2),  
(100 ,'2017-04-01',NULL,'active ',3),  
(100 ,'2017-05-01', NULL,'churned', 3),  
(100 ,'2021-02-01','2021-02-01', 'active', 1),  
(100 ,'2021-03-01', NULL,'active ',2),  
(100 ,'2021-04-01', NULL,'active', 3),  
(100 ,'2021-05-01', NULL,'active', 4),  
(100 ,'2021-06-01', NULL,'active', 5),  
(100 ,'2021-07-01', NULL,'active ',6)  
  
select id,[month],Sub_created=MAX(Sub_created) OVER (PARTITION BY c),status,tenure  
from   
(  
select *,c=COUNT(Sub_created) OVER (PARTITION BY id ORDER BY [month])  
 from tableaj   
) d  
 order by [month]  

Output:

id	month	Sub_created	status	tenure  
100	2017-02-01	2017-02-01	active	1  
100	2017-03-01	2017-02-01	active	2  
100	2017-04-01	2017-02-01	active 	3  
100	2017-05-01	2017-02-01	churned	3  
100	2021-02-01	2021-02-01	active	1  
100	2021-03-01	2021-02-01	active 	2  
100	2021-04-01	2021-02-01	active	3  
100	2021-05-01	2021-02-01	active	4  
100	2021-06-01	2021-02-01	active	5  
100	2021-07-01	2021-02-01	active 	6  

Best regards,
Melissa

---

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.

Answer 2

The simple solution:

SELECT a.ID, a.Month, b.Sub_Created, a.status, a.tenure
FROM   tbl a
OUTER APPLY (SELECT TOP (1) b.Sub_Created
              FROM  tbl b 
              WHERE b.ID = a.ID
                  AND b.Month <= a.Month
              ORDER BY b.Month DESC) AS b

This is not the most efficient solution, but it should be good enough for smaller data sets. The more efficient solution is more complicated, and I don't recall it by heart.

Also, note that for this type of question, it is always a good idea to post the CREATE TABLE statement for your table(s), and the sample data as INSERT statements. This makes it easy to copy and paste into a query window to produce a tested solution. The solution above is not-tested, only typed.