I have data that looks like this:

after processing using this query:

WITH CTE (customerID,FirstWeek,RN) AS (
        SELECT customerID,MIN(DATEPART(week,tp_date)) TransWeek,
        ROW_NUMBER() over(partition by customerID ORDER BY DATEPART(week,tp_date) asc ) FROM all_table
        GROUP BY customerID,DATEPART(week,tp_date)
    ) 
    
    SELECT CTE.customerID, CTE.FirstWeek,  
         (select TOP 1 (DATEPART(week,c.tp_date))   
            from all_table c 
                where c.customerID = CTE.customerID AND DATEPART(week,C.tp_date) > CTE.FirstWeek 
                    )   SecondWeek 
    FROM CTE  
    WHERE RN = 1

the result is like this

but the results will be appropriate when using the weeknum in excel. but what i hope the result is isoweek format which will look like this

CodePudding user response：

Datepart has an isowk parameter. So instead of

DATEPART(week,...

use

DATEPART(isowk,...

CodePudding user response：

Have create a small fiddle to help: db<>fiddle

And as I understand I would just use min and max values of weeks grouped by customer