Pivot single table multiple times with multiple Max Values

SSAS and SSRS
1

Hello,

I have a rather interesting issue si am trying to figure out how to solve. I have a table called PART and I need to cross reference that with a table called PURCHASE. The part table looks as follows:

 PART.PartNum | PART.Qty
    A1        | 12
    B1        |  2
    C1        |  5

The PURCHASE table looks as follows:

PURCHASE.PartNum | PURCHASE.LastReceviedDate | PURCHASE.Qty | PURCHASE.CostPer
A1               |  12/12/2017               |  5           | 15.00
A1               |  02/12/2017               |  24          | 22.00
A1               |  11/15/2017               |  36          | 12.00
B1               |  12/06/2017               |  2           | 9.50
B1               |  12/02/2017               |  3           | 7.00
C1               |  11/16/2017               |  2           | 55.00
C1               |  11/03/2017               |  2           | 34.00
C1               |  10/22/2017               |  2           | 23.00
C1               |  10/15/2017               |  2           | 34.00

So based on those tables. I need to match up the part num and part qtys to determine on many parts that are left in the first table are at what PURCHASE.CostPer. E.g. Partnum "A1" Has has a qty of 12 in stock. So therefore, I have according to the PURCHASE table based on the "LastReceivedDate" a qty of 5 at $15.00 each and a qty of 7 at $22.00 each.

Ideally I would like to see the output something like this:

OUTPUT.PartNum | OUTPUT.Qty | OUTPUT.CostPer
A1             | 5          | 15.00
A1             | 7          | 22.00
B1             | 2          | 9.50
C1             | 2          | 55.00
C1             | 2          | 34.00
C1             | 1          | 23.00

I am, a bit lost on where to start here, so any help at all would be greatly appreciated.

2

something like:

select 
     pa.partnum,
     count(*) over (partition by pa.partnum, pu.LastReceivedDate) as qty
     max(pu.costper) over (partition by pa.partnum, pu.LastReceivedDate) as costper
from part pa
join purchase pu
on pa.partnum = pu.partnum
order by pa.partnum