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?
