Return only earliest status change results

Clem 40 Reputation points

I am trying to get the first time a record changes status and only that time. The status Being Processed has multiple dates/times attached to it. Same for Received.Status Change Code

Status Changes Results

For 31181808224681 I only want the 2023-01-23 15:30:07:810 for Being Processed. For 31181508224699 I only want the 2023-01-23-15:30:07.747 to be returned.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 17,156 Reputation points Microsoft Vendor

    Hi @Clem

    It seems you shouldn't add the irh2.TransactionDate in your GROUP BY clause. User's image

    You could use MIN() function inside the DATEDIFF function directly.

    Try modifying the SELECT and GROUP BY clause like below:

    SELECT cir.barcode,
           irh1.TransactionDate AS Received,
    	   MIN(irh1.TransactionDate) AS BeingProcessed,
    	   DATEDIFF(DAY,irh1.TransactionDate,MIN(irh1.TransactionDate)) AS DAYS
    FROM ...
    WHERE ...
    GROUP BY cir.barcode,irh1.TransactionDate
    ORDER BY ...

    Best regards,

    Cosmog Hong

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Jingyang Li 5,806 Reputation points

    select * from (

    select * , row_number() over(partition by barcode, received order by beingprocessed ) rn

    from yourquery) t

    where rn=1