Count number of days since change

Question

Hi all

I have data that looks like image below. I have date, customer, amount and if the amount is above 750 I have a column called trigger. What I want is to count the number of days in a row that the trigger is 1 (or amount above 750) for each customer using t-sql. This can go across months, years and be hundreds of days. I'm working with Azure SQL DB so it's the newest SQL Server version.

I have tried many things but not gotten to where I want. I suspect I need some kind of recursive SQL but I'm not good enough to figure it out. Searching the internet gives me little unfortunately.

All help appreciated.

Ásgeir

Answer 1

Hi @Ásgeir Gunnarsson ,

Thank you so much for posting here.

Please refer below simple way:

create table mytable ( [date] date, CustId int, Amount int,[trigger] bit)  
       
 insert mytable values   
 ( '01-11-2020', 200, 3535, 1 ),  
 ( '02-11-2020', 200, 0, 0 ),  
 ( '03-11-2020', 200, 0,0 ),  
 ( '04-11-2020', 200, 0,0 ),  
 ( '05-11-2020', 200, 4162, 1 ),  
 ( '06-11-2020', 200, 4162,1 ),  
 ( '07-11-2020', 200, 4162,1 ),  
 ( '08-11-2020', 200, 4162,1 ),                           
 ( '01-11-2020', 300, 0, 0 ),  
 ( '02-11-2020', 300, 800, 1 ),  
 ( '03-11-2020', 300, 0,0 ),  
 ( '04-11-2020', 300, 0,0 ),  
 ( '05-11-2020', 300, 1600,1 ),  
 ( '06-11-2020', 300, 1600,1 ),  
 ( '07-11-2020', 300, 1600,1 ),  
 ( '08-11-2020', 300, 0,0 )  
  
 select *,  
 case when [trigger]=1 then row_number() over (partition by CustId,Amount,[trigger] order by [Date]) else 0 end  [COLUMN I WANT]  
 from mytable  
 order by CustId,date  

Output:

date	CustId	Amount	trigger	COLUMN I WANT  
2020-11-01	200	3535	1	1  
2020-11-02	200	0	0	0  
2020-11-03	200	0	0	0  
2020-11-04	200	0	0	0  
2020-11-05	200	4162	1	1  
2020-11-06	200	4162	1	2  
2020-11-07	200	4162	1	3  
2020-11-08	200	4162	1	4  
2020-11-01	300	0	0	0  
2020-11-02	300	800	1	1  
2020-11-03	300	0	0	0  
2020-11-04	300	0	0	0  
2020-11-05	300	1600	1	1  
2020-11-06	300	1600	1	2  
2020-11-07	300	1600	1	3  
2020-11-08	300	0	0	0  

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.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 2

Check one of possible approaches:

declare @table table ( [date] date, CustId int, [trigger] bit)

set dateformat dmy

insert @table ([date], CustId, [trigger]) values 
( '01-11-2020', 200, 1 ),
( '02-11-2020', 200, 0 ),
( '03-11-2020', 200, 0 ),
( '04-11-2020', 200, 0 ),
( '05-11-2020', 200, 1 ),
( '06-11-2020', 200, 1 ),
( '07-11-2020', 200, 1 ),
( '08-11-2020', 200, 1 ),

( '01-11-2020', 300, 0 ),
( '02-11-2020', 300, 1 ),
( '03-11-2020', 300, 0 ),
( '04-11-2020', 300, 0 ),
( '05-11-2020', 300, 1 ),
( '06-11-2020', 300, 1 ),
( '07-11-2020', 300, 1 ),
( '08-11-2020', 300, 0 )

;
with E as
(
    select *,
        max(case [trigger] when 0 then [Date] end) over (partition by CustId order by [date]) m
    from @table t
)
select [date], CustId, [trigger], 
    case [trigger] when 1 then row_number() over (partition by CustId, [trigger], m order by [Date]) else 0 end as [COLUMN I WANT]
from E
order by CustId, [date]

(Amount is not shown, assuming that it cannot be used for an alternative approach)..

Answer 3

Thank you both for your answers. Viorel-1 your answer gave me 1 for each row but didn´t count the number of rows from when trigger first was 1 until it changed for the customer.

Melissa. Your answer was exactly what I needed. Much simpler than I would have imagine and pretty fast. I´m very grateful for your help.

Ásgeir