Return only earliest status change results

Question

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.

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.

Answer 1

Hi @Clem

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

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.

Answer 2

select * from (

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

from yourquery) t

where rn=1