Let's say I have data like this

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

My query:

CREATE TABLE trydata
(
    CustomerID CHAR(7) not null,
    CustTrans date,
    CustSales int,
)
insert into trydata(CustomerID,CustSales,CustTrans)
    values('C001',34,'2022-09-03'),('C002',23,'2022-09-02'),('C003',132,'2022-09-03'),
    ('C004',95,'2022-09-02'),('C002',68,'2022-09-08'),('C001',54,'2022-09-05'),
    ('C002',34,'2022-09-11'),('C002',98,'2022-09-23'),('C004',34,'2022-09-19'),
    ('C001',30,'2022-09-18'),('C003',34,'2022-09-26');

SELECT m.CustomerID, m.Trans_week, n.first as first
FROM (
    SELECT CustomerID,DATEPART(week,CustTrans) AS Trans_week
    FROM trydata
    GROUP BY CustomerID,DATEPART(week,CustTrans)
) m, (
    SELECT CustomerID,min(DATEPART(week,CustTrans)) AS first
    FROM trydata
    GROUP BY CustomerID
) n
WHERE m.CustomerID = n.CustomerID;

CodePudding user response：

Your request seem unclear to me. We will refactor code after we clear this out.

Why Customer C001 has first row 36 if trans week is 39 in your desired output ?

This will give you Min,Max for each customerID row

SELECT CustomerID,DATEPART(week,CustTrans) TransWeek,
  (select MIN(DATEPART(week,CustTrans)) 
    from trydata c 
    where c.CustomerID = trydata.CustomerID)    AS FirstWeek,
  (select MAX(DATEPART(week,CustTrans)) 
    from trydata c 
    where c.CustomerID = trydata.CustomerID)    AS SecondWeek
FROM trydata
Order by CustomerID

And this will give you Min, and higher than Min for each customerID

SELECT *, isnull((select TOP 1 (DATEPART(week,CustTrans))   
                from trydata c 
                    where c.CustomerID = SRC.CustomerID AND DATEPART(week,C.CustTrans) > SRC.TransWeek 
                    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

But none of those answers doesn't match your requested output.

Also this one for me , make more sense, if you want to get Min,Max (Top,Last) order week for each Customer

SELECT CustomerID,
  (select MIN(DATEPART(week,CustTrans)) 
    from trydata c 
    where c.CustomerID = trydata.CustomerID)    AS FirstWeek,
  (select MAX(DATEPART(week,CustTrans)) 
    from trydata c 
    where c.CustomerID = trydata.CustomerID)    AS SecondWeek
FROM trydata
Group by CustomerID

CodePudding user response：

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 DISTINCT CustomerID,
       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
WHERE rn <= 2
ORDER BY CustomerID

See a demo.