question

Matty-4135 avatar image
0 Votes"
Matty-4135 asked Matty-4135 commented

Calculate streak length with conditions...

Hi Team,

Looking for some help to calculate the length of a streak based on a specific set of conditions.

Data is as follows:

101816-image.png


I need to calculate the length of the 'Indicator' streaks, partitioned by the Town, Shop, Item and Monday_Date. However, the streak counter should reset if Monday_Dates aren't consecutive, i.e. 7 days between them. Here's what would be returned:

101846-image.png

Finally, I'm looking for a summary something like this:

101735-image.png



Hope this is clear. I've had a play with row_count and lag functions, but I'm struggling to knit everything together.

Thanks in advance.

Matty

sql-server-transact-sql
image.png (25.4 KiB)
image.png (27.1 KiB)
image.png (21.4 KiB)
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.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered Matty-4135 commented

When asking a question, please give sample data with CREATE TABLE or DECLARE TABLE and INSERT statements. Giving sample data as an image makes it extremely difficult to use. Giving the desired result as an image is fine. That said. try

 Declare @Sample Table(Town varchar(20), Shop char(1), Item varchar(20), Monday_Date date, Indicator int);
 Insert @Sample(Town, Shop, Item, Monday_Date, Indicator) Values
 ('Somewhere', 'A', 'Apple', '20201026', 1),
 ('Somewhere', 'A', 'Apple', '20201102', 1),
 ('Somewhere', 'A', 'Apple', '20201109', -1),
 ('Somewhere', 'A', 'Apple', '20201116', -1),
 ('Somewhere', 'A', 'Apple', '20201123', -1),
 ('Somewhere', 'A', 'Apple', '20210104', -1),
 ('Somewhere', 'A', 'Apple', '20210111', -1),
 ('Somewhere', 'A', 'Apple', '20210118', -1),
 ('Somewhere', 'B', 'Pear', '20201228', -1),
 ('Somewhere', 'B', 'Pear', '20210104', 1),
 ('Somewhere', 'B', 'Pear', '20210111', 1),
 ('Somewhere', 'B', 'Pear', '20210118', 1),
 ('Somewhere', 'B', 'Pear', '20210125', -1),
 ('Somewhere', 'B', 'Pear', '20210201', 1),
 ('Somewhere Else', 'C', 'Apple', '20201026', -1),
 ('Somewhere Else', 'C', 'Apple', '20201102', 1),
 ('Somewhere Else', 'C', 'Apple', '20201109', 1);
    
 ;With cteIslands As
 (Select Town, Shop, Item, Monday_Date, Indicator, 
 Row_Number() Over(Partition By Town, Shop, Item, Indicator Order By Monday_Date) - DateDiff(day, '19000101', Monday_Date) / 7 As Island 
 From @Sample),
    
 cteGroups As
 (Select Town, Shop, Item, 
   Min(Monday_Date) As First_Monday_Date,
   Max(Monday_Date) As Last_Monday_Date
 From cteIslands
 Group By Town, Shop, Item, Indicator, Island)
    
 Select Town, Shop, Item, First_Monday_Date, Last_Monday_Date,
   Row_number() Over(Partition By Town, Shop, Item Order By First_Monday_Date) As Streak_Number,
   DateDiff(day, First_Monday_Date, Last_Monday_Date) / 7 + 1 As Streak_Length
 From cteGroups
 Order By  Town, Shop, Item, First_Monday_Date;

Tom

· 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.

Thanks Tom. That does exactly what I need.

Point take regarding the images - I'm fairly new here and hadn't released they were being posted as images as opposed to copy-able tables.

Cheers,

Matty

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered Matty-4135 commented

Hi @Matty-4135,

101857-image.png

Why are 2020-10-26 and 2020-11-02 divided into one group?

Please try:

 CREATE TABLE #test (Town varchar(20), Shop char(1), Item varchar(20), Monday_Date date, Indicator int);
 INSERT INTO #test Values
  ('Somewhere', 'A', 'Apple', '20201026', 1),
  ('Somewhere', 'A', 'Apple', '20201102', 1),
  ('Somewhere', 'A', 'Apple', '20201109', -1),
  ('Somewhere', 'A', 'Apple', '20201116', -1),
  ('Somewhere', 'A', 'Apple', '20201123', -1),
  ('Somewhere', 'A', 'Apple', '20210104', -1),
  ('Somewhere', 'A', 'Apple', '20210111', -1),
  ('Somewhere', 'A', 'Apple', '20210118', -1),
  ('Somewhere', 'B', 'Pear', '20201228', -1),
  ('Somewhere', 'B', 'Pear', '20210104', 1),
  ('Somewhere', 'B', 'Pear', '20210111', 1),
  ('Somewhere', 'B', 'Pear', '20210118', 1),
  ('Somewhere', 'B', 'Pear', '20210125', -1),
  ('Somewhere', 'B', 'Pear', '20210201', 1),
  ('Somewhere Else', 'C', 'Apple', '20201026', -1),
  ('Somewhere Else', 'C', 'Apple', '20201102', 1),
  ('Somewhere Else', 'C', 'Apple', '20201109', 1);
    
 ;WITH cte
 as(
 SELECT Town, Shop, Item,Indicator,
 MIN(Monday_Date) First_Monday_Date,
 MAX(Monday_Date) Last_Monday_Date 
 ,ROW_NUMBER() OVER(PARTITION BY Town, Shop, Item ORDER BY Town) Streak_Number
 FROM #test
 GROUP BY Town, Shop, Item,Indicator,DATEPART(MM,Monday_Date))
    
 SELECT *,DateDiff(day, First_Monday_Date, Last_Monday_Date) / 7 + 1 As Streak_Length
 FROM cte
 ORDER BY Shop,Streak_Number


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.



image.png (29.6 KiB)
· 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.

Thanks for your input, Echo. I have already implemented the solution provided by Tom, but I will test your solution when I get chance.

Cheers,

Matty

0 Votes 0 ·