T-SQL MAX Window Function not working as expected

Question

Here is my query:

SELECT  
    j.inv_num,
    CAST(j.inserteddatetime AS DATE) AS [CostedDate],
    MIN(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num ORDER BY CAST(j.inserteddatetime AS DATE)) AS [FirstCostedDate],
    MAX(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num ORDER BY CAST(j.inserteddatetime AS DATE)) AS [LastCostedDate]
FROM 
    dbo.JobCost AS j WITH (NOLOCK)
WHERE 
    J.INV_NUM = '353197'

Here is the result

inv_num CostedDate  FirstCostedDate LastCostedDate
--------------------------------------------------
353197  2023-12-13  2023-12-13  2023-12-13
353197  2023-12-21  2023-12-13  2023-12-21
353197  2023-12-21  2023-12-13  2023-12-21

I took out the [Costed Date] from the query and did a group by j.inv_num. It worked as expected. However, I need the 3 "CostedDate" values that the inv_num was inserted into this table along with the MIN & MAX.

I thought the first row in the result set would have '2023-12-21' as the "LastCostedDate".

What am I missing? Thanks!

Answer 1

This is returned based on the default window frame which is between unbounded preceding and current row. To get the results you want you can specify the window frame explicitly by adding the following after your "order by"

rows between unbounded preceding and unbounded following