SELECT
-- In practice the AssetID, IssueTrackingSystem, and MD_Date_DateIntegerFY are the business key
itav.MF_AssVal_AssetID,
itav.MF_AssVal_IssueTrackingSystem,
itav.MD_ITAsset_SurrogateKey,
itav.MD_ITUser_SurrogateKey,
itav.MD_Building_SurrogateKey,
itav.MD_Room_SurrogateKey,
itav.MD_CarlDept_SurrogateKey,
itav.MD_CollPers_SurrogateKeyEmp,
itav.MD_CollPers_SurrogateKeyDStu,
itav.MD_Date_PurchaseDate,
CASE
WHEN itav.MD_Date_PurchaseDate IS NULL OR itav.MD_Date_PurchaseDate >= 99990000 THEN 99991231
WHEN ita.Cycle IS NULL OR ita.Cycle >= 99 THEN 99991231
ELSE itav.MD_Date_PurchaseDate + (ita.Cycle * 10000)
END AS MD_Date_ReplacementDate,
itav.MD_Date_DisposalDate,
-- Allows join to VMD_FiscalYear
mdd.MD_Date_DateInteger AS MD_Date_DateIntegerFY,
itav.MF_AssVal_CarletonCost,
CASE
WHEN d.MD_Date_FiscalYear IS NULL THEN NULL
WHEN mdd.MD_Date_FiscalYear IS NULL THEN NULL
WHEN d.MD_Date_FiscalYear <= 1900 THEN NULL
WHEN mdd.MD_Date_FiscalYear >= 9999 THEN NULL
ELSE mdd.MD_Date_FiscalYear - d.MD_Date_FiscalYear
END AS MF_AssVal_AgeInFiscalYears,
CAST(CASE
WHEN d.MD_Date_FiscalYear IS NULL THEN NULL
WHEN mdd.MD_Date_FiscalYear IS NULL THEN NULL
WHEN d.MD_Date_FiscalYear <= 1900 THEN NULL
WHEN mdd.MD_Date_FiscalYear >= 9999 THEN NULL
WHEN itav.MF_AssVal_CarletonCost IS NULL THEN NULL
-- If Cycle is <= 0 then the asset is worth nothing, because Cycle = 0 means it should be disposed of
WHEN ita.Cycle = 0                              THEN .05 * itav.MF_AssVal_CarletonCost
WHEN ita.Cycle < 0                              THEN 0
-- If an asset is > Cycle (default = 5) years old, it's worth nothing (evens out, because if we wait it'll be worth less than zero [salvage cost])
WHEN (mdd.MD_Date_FiscalYear - d.MD_Date_FiscalYear) > ISNULL(ita.Cycle, 5) THEN 0
ELSE
-- Book value = original value - cumulative depreciation
-- Cumulative depreciation = (original cost - salvage value) divided by Cycle (default = 5 years), multiplied by the age in fiscal years
-- Salvage value is assumed to be 5% of the original value
itav.MF_AssVal_CarletonCost - (
((itav.MF_AssVal_CarletonCost - (.05 * itav.MF_AssVal_CarletonCost)) / ISNULL(ita.Cycle, 5))
* (mdd.MD_Date_FiscalYear - d.MD_Date_FiscalYear))
END AS NUMERIC(18,2)) AS MF_AssVal_BookValue,
itav.MF_AssVal_CostCenter,
itav.MF_AssVal_CountWithKnownCost,
itav.MF_AssVal_Count
FROM
MF_IssueTrackingAssetValue itav
INNER JOIN (
    SELECT
    MD_ITASSET_SURROGATEKEY,
    CASE
    WHEN MD_ITAsset_YearsUntilReplacement >= 99 THEN NULL
    ELSE MD_ITAsset_YearsUntilReplacement
    END AS Cycle
FROM
       MD_ISSUETRACKINGASSET
) ita
ON itav.MD_ITASSET_SURROGATEKEY = ita.MD_ITASSET_SURROGATEKEY
-- Get fiscal year of original purchase
LEFT OUTER JOIN MD_Date d
-- VMD_FiscalYear contains only days in MD_Date that end fiscal years
ON d.MD_Date_IsLastDayOfFiscalYear = 'Yes'
AND itav.MD_Date_PurchaseDate <= d.MD_Date_DateInteger
-- Subtracting 10000 from a date integer rolls the date integer back one year
AND itav.MD_Date_PurchaseDate > (d.MD_Date_DateInteger - 10000)
-- COMMENTED OUT BECAUSE WE WANT TO SEE ASSETS THAT ARE GONE/DISPOSED OF
-- AND ISNULL(itav.MD_Date_DisposalDate, 99991231) >= (d.MD_Date_DateInteger - 1000)
-- Get all the fiscal years from date of purchase, up through the disposal year
LEFT OUTER JOIN MD_Date mdd
-- VMD_FiscalYear contains only days in MD_Date that end fiscal years
ON mdd.MD_Date_IsLastDayOfFiscalYear = 'Yes'
-- Adding 150000 to a date integer, e.g., rolls the date integer forward fifteen years
AND mdd.MD_Date_DateInteger <= (d.MD_Date_DateInteger + 100000)
-- Subtracting 10000 from a date integer rolls the date integer back one year
AND mdd.MD_Date_DateInteger > (d.MD_Date_DateInteger - 10000)
-- COMMENTED OUT BECAUSE WE WANT TO SEE ASSETS THAT ARE GONE/DISPOSED OF
-- AND ISNULL(itav.MD_Date_DisposalDate, 99991231) > (mdd.MD_Date_DateInteger - 10000)
-- Go out three years past the present day
AND mdd.MD_Date_DateInteger <= ((YEAR(GETDATE()) * 10000) + 30630)
WHERE
itav.MD_Date_PurchaseDate IS NOT NULL