This is the same question as
How to find maximum of date if group ids (particular group) present, else latest date where all group ids present[
^]
I have already provide a solution to that post giving exactly the results you said you wanted.
You have subsequently said
Quote:
I have updated my question here, this is actual requirement as above query is not giving result what is expected for me.
and yet the results from my solution were identical to the results you said you wanted in your question and it even takes into account the rundate. So in what way does that solution not fit? What are YOU not telling us?
To get the results you require go to your original question, use the code from my solution, changing my tablename
@Tab
to your table name
intrans
and use
declare @rundate date = '2025-12-01';
instead of the value I provided.