Calculation of rank according to supplier table & sales table

I’m working with SQL Server 2014; I attached a purchase table (from suppliers) and sales table.

First, I need to use a sales table based on the rankings from the purchase table, product bought first from supplier table sold first in sales table (FIFO method).

How do I do these calculations?

Attached is a rank calculation in the purchase table.

Thanks.

Tables:

CREATE TABLE Purchases
(
     PurID INT,
     ItemID VARCHAR(5),
     Qty INT,
     UnitPrice INT
)

INSERT INTO Purchases (PurID, ItemID, Qty, UnitPrice) 
VALUES ('1', 'A', '50', '3'),
       ('2', 'A', '60', '2'),
       ('3', 'A', '90', '6'),
       ('5', 'B', '5', '5'),
       ('6', 'B', '15', '4')

CREATE TABLE Sales_5
(
     InvID INT,
     ItemID VARCHAR(5),
     Qty INT,
     UnitPrice_Fifo INT
)

INSERT INTO Sales_5 (InvID, ItemID, Qty, UnitPrice_Fifo)
VALUES ('1', 'A', '100', '250'),
       ('2', 'A', '100', '560'),
       ('5', 'B', '10', '45'),
       ('6', 'B', '10', '40')

Rank from table Purchases:

 with PurchasesbyFifo AS 
 (
     select 
         itemid,
         Purid,
         rank () over (partition by itemid order by Purid ASC) as rank_BY_Fifo 
     from
         [Purchases] 
 )

Answer

Try this …

Select X.*
From
(
    select 
        P.ItemID, 
        P.PurId, 
        Rank () Over 
        (
            Partition By 
            (
                Select Min(S.InvID)
                From Sales_5 As S With(NoLock)
                Where S.ItemID = P.ItemID
            ) 
            Order By P.PurId ASC
        )
        As rank_BY_Fifo 
    from Purchases As P With(NoLock)
) As X
Order By rank_BY_Fifo