Aggregation view with Min and Max

Other SQL Server Topics
1

Hello,
I'm trying to create an aggregate view for the sample data below. I want the warehouse_key of the Minimum invoice date for each order but also in display I need to show the Max of Invoice date.

Table A -

OrderNo LineNo Header_Key Invoice_date warehouse_key
ABC 1 sdkj 29/09/2023 d447
ABC 2 sdjk 29/09/2023 d447
ABC 3 sdjk 3/10/2023 eecc
ABC 4 sdjk 3/10/2023 eecc

SQL I tried -
select
Order Number,
Header_Key,
Max(processing_days),
Max(Invoice_date), --- In the view we need to display Maximum invoice date but warehouse key should
be of the minimum invoice date.
(select (Warehouse_Key
from TableA
where Invoice_date in (select distinct min(Invoice_date) over(partition by "Order Number" order by "Invoice_date" asc) from "TableA" ) as "Warehouse_KEY
From Table A
group by 1,2

Throws me an error. Appreciate any inputs.

2

As there is no consumable test data, this has not been tested.

WITH Orders
AS
(
	SELECT OrderNo
		,MIN(Header_Key) AS Header_Key
		,MIN(CONCAT(CONVERT(char(8), Invoice_date, 112), warehouse_key)) AS DateKey
		,MAX(Invoice_date) AS Max_Invoice_date
	FROM YourTable
	GROUP BY OrderNo
)
SELECT OrderNo, Header_Key
	,SUBSTRING(DateKey, 9, 255) AS warehouse_key
	,CAST(LEFT(DateKey, 8) AS date) AS Min_Invoice_date
	,Max_Invoice_date
FROM Orders;

If you post again you may want to look at the following first.