I have a table in SQL Server called `ShippingDocSummary` which contains all historical records for shipping documents. For a given document number, there can be any number of historical records/transactions. What I am trying to do is create a view that summarizes that table and calculates various column values. My issue comes when trying to get a specific value from the source table based on a calculated column value from the same source table.

Here is a simplified example of what the source table looks like:

```
DocNum    DocHistID    StatusCode   StatusQty    StatusDate
12345     10000001     AS1          2            2/16/2020
12345     10000002     D6T          2            4/20/2020
12345     10000003     COR          2            4/20/2020
12345     10000004     AS1          2             5/5/2020

```

My code to create my summary so far is as follows:

```sql
SELECT
  DocNum,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN StatusQty ELSE 0 END) AS AS_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'D6T' THEN StatusQty ELSE 0 END) AS D6T_QTY,
  SUM(CASE WHEN ShippingDocSummary.StatusCode LIKE 'COR' THEN StatusQty ELSE 0 END) AS COR_QTY,
  MAX(CASE WHEN ShippingDocSummary.StatusCode LIKE 'AS_' THEN DocHistID ELSE null END) AS LastAS_DHID
FROM ShippingDocSummary
GROUP BY DocNum

```

This gives me the following, which is correct:

```
DocNum    AS_QTY    D6T_QTY    COR_QTY    LastAS_DHID
12345     4         2          2          10000004

```

What I am trying to get next, as a column, is the `StatusDate` that correlates to the `LastAS_DHID` value that was calculated from the select (i.e. I need the next column to show '5/5/2020' since that date ties to the `DocHistID` '10000004').

In Power Bi, I'd use a `lookupvalue` function, but as I transfer my logic to the SQL server, I think I need to use a derived `CASE WHEN` type of query, but unsure of how to structure the syntax. Any help is greatly appreciated!