CODEWITHSUNDEEP
sqlsql-serverdatabase
tasya fauzia fitriasari
tasya fauzia fitriasari

Reputation: 37

Calculate and find the second day of the week in SQL

Let's say I have data like this

CustomerID Trans_date
C001 01-sep-22
C001 04-sep-22
C001 14-sep-22
C002 03-sep-22
C002 01-sep-22
C002 18-sep-22
C002 20-sep-22
C003 02-sep-22
C003 28-sep-22
C004 08-sep-22
C004 18-sep-22

But I'm unable to find the first and second transaction based on Trans_date. I wish for the result to look like this:

CustomerID Trans_week first second
C001 35 35 37
C001 35 35 37
C001 37 35 37
C002 35 35 37
C002 35 35 37
C002 37 35 37
C002 38 35 37
C003 35 35 39
C003 39 35 39
C004 36 36 37
C004 37 36 37

And for the last result will show like this:

CustomerID first second
C001 35 37
C002 35 37
C003 35 39

C004 didnt include because i would need who cust id who come first in their 1st week.

Upvotes: 1

Views: 110

Answers (2)

Anel Hodžić
Anel Hodžić

Reputation: 313

with cte (RN,CustomerID, FirstWeek,SecondWeek ) as
( SELECT ROW_NUMBER() over(partition by CustomerID  ORDER BY CustomerID  )  RN, CustomerID,FirstWeek, isnull((select TOP 1 (DATEPART(week,CustTrans))   
                from trydata c 
                    where c.CustomerID = SRC.CustomerID AND DATEPART(week,C.CustTrans) > SRC.FirstWeek  
                    ORDER BY DATEPART(week,C.CustTrans) ),'0')  AS SecondWeek 
FROM (  
    SELECT CustomerID,DATEPART(week,CustTrans) TransWeek,
      (select MIN(DATEPART(week,CustTrans)) from trydata c where c.CustomerID = trydata.CustomerID) AS FirstWeek
    FROM trydata
) SRC )

select CustomerID,FirstWeek,SecondWeek from cte where RN = 1

Output:

Example 2 :

WITH CTE (CustomerID,FIrstWeek,RN) AS (
    SELECT CustomerID,MIN(DATEPART(week,CustTrans)) TransWeek,
    ROW_NUMBER() over(partition by CustomerID  ORDER BY DATEPART(week,CustTrans) asc ) FROM TryData 
    GROUP BY CustomerID,DATEPART(week,CustTrans)
) 

SELECT CTE.CustomerID, CTE.FIrstWeek,  
     (select TOP 1 (DATEPART(week,c.CustTrans))   
        from trydata c 
            where c.CustomerID = CTE.CustomerID AND DATEPART(week,C.CustTrans) > CTE.FIrstWeek 
                )   SecondWeek 
FROM CTE  
WHERE RN = 1

FIddle Demo

Edit: This can be done on easier way and less complex.

Upvotes: 1

ahmed
ahmed

Reputation: 9191

You may use ROW_NUMBER() function -inside a subquery- to get the first and second transaction dates for a customer, then use conditional MAX window function on the results of that subquery.

SELECT CustomerID, DATEPART(week,CustTrans) AS Trans_week,
       DATEPART(week, MAX(CASE rn WHEN 1 THEN CustTrans END) OVER (PARTITION BY CustomerID)) first,
       DATEPART(week, MAX(CASE rn WHEN 2 THEN CustTrans END) OVER (PARTITION BY CustomerID)) second
FROM
  (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustTrans) rn
    FROM trydata
  ) T
ORDER BY CustomerID, Trans_week

See a demo on SQL Server.

As you requested in the comments, if you want to select only one row per customer that showing the first and second weeks, use the following query:

SELECT CustomerID,
   DATEPART(week, MAX(CASE rn WHEN 1 THEN CustTrans END)) first,
   DATEPART(week, MAX(CASE rn WHEN 2 THEN CustTrans END)) second
FROM
  (
    SELECT *,
      ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY CustTrans) rn
    FROM trydata
  ) T
WHERE rn <= 2
GROUP BY CustomerID
ORDER BY CustomerID

See a demo.

Upvotes: 1

Related Questions