Case statements and mapping for last 12 months

[#1]

I have the following within my database:

DailyInventory:

Load

*;

SQL SELECT

ds.inventoryKey as masterid,

i.ICode,

i.Description as ICodeDescription,

i.Category,

i.SubCategory,

w.warehouseid,

w.warehouse,

Date as TransactionDate,

OwnedQty,

(SELECT mw.cost from Rentalworks.dbo.masterwh mw where mw.masterid = ds.InventoryKey and mw.warehouseid = w.warehouseid)currentcost

FROM RentalWorksDW.dbo.DailyInventoryStatus ds

INNER JOIN RentalWorksDW.dbo.Inventory i on i.InventoryKey = ds.InventoryKey

INNER JOIN RentalWorks.dbo.Warehouse w on w.warehouseid = ds.WarehouseKey

WHERE i.AvailFor='SALE'

AND i.InactiveFlag<>'T'

AND (w.inactive<>'T' OR w.warehouse IN ('HALIFAX'))

Order by Date;

/* ---------- Sales and Purchases ----*/

[SalesAndPurchases]:

Load

warehouse,

masterid,

//masterno,

//ICodeDescription,

Date(TransactionDate, 'YYYY-MM-DD') as TransactionDate,

TransactionType,

orderno,

UnitPurchasePrice,

PurchaseQty,

ExtendedCostPrice;

SQL SELECT

sc.warehouse,

RTRIM(sc.InventoryKey) as masterid,

//RTRIM(sc.ICode) as masterno,

//RTRIM(ICodeDescription) as ICodeDescription,

sc.TransactionDate,

sc.TransactionType,

REVERSE(SUBSTRING(REVERSE(RTRIM(sc.ChangeDescription)), 1,

  CHARINDEX(' ', REVERSE(RTRIM(sc.ChangeDescription))) - 1)) as orderno,

case when (sc.TransactionType='PURCHASE') then UnitPriceAddition

   when (sc.TransactionType ='VENDOR RETURN') then UnitPriceSubtraction

   end as UnitPurchasePrice,

case when (sc.TransactionType='PURCHASE') then AdditionQty

    when (sc.TransactionType='VENDOR RETURN') then SubtractionQty

  end as PurchaseQty,

case when (sc.TransactionType='PURCHASE') then ExtendedAdditionPrice

   when (sc.TransactionType='VENDOR RETURN') then ExtendedSubtractionPrice

   end as ExtendedCostPrice

FROM PSRentalworksDW.dbo.SalesInventoryChange sc

WHERE (sc.TransactionType ='PURCHASE' OR sc.TransactionType='VENDOR RETURN')

AND sc.TransactionDate >= '2013-01-01';

[Sales]:

CONCATENATE (SalesAndPurchases)

Load

warehouse,

masterid,

//masterno,

//ICodeDescription,

Date(TransactionDate, 'YYYY-MM-DD') as TransactionDate,

TransactionType,

orderno,

UnitSalePrice,

SalesQty,

ExtendedSalesPrice;

SQL SELECT

es.warehouse,

RTRIM(es.masterid) masterid,

//es.masterno,

//es.description as ICodeDescription,

es.transdate as TransactionDate,

es.transtype as TransactionType,

es.orderno,

es.price as UnitSalePrice,

es.qtyordered as SalesQty,

es.priceextended as ExtendedSalesPrice

FROM RWReports.dbo.ExpendableSales es;

Now In order to calculate Average Inventory Value for the last 12 months, I need to use case statements or something similar

where if the Purchase Date is within the last 12 months, it will use that as the Cost price for the item and then use that date for the Daily Inventory to get the value that date. How would I go about doing this?