question

ajax6785-3746 avatar image
0 Votes"
ajax6785-3746 asked ajax6785-3746 commented

SQL Code to have same date for multiple rows

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ajax6785-3746 commented

Hi @ajax6785-3746

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Perfect. Thank you !!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.