Average Cost By Material Type

Clearinghouse Repository Polaris Repository
1

This was a response to a request on Discord, looking to modify the “Expenditures by Material Type and Fund” canned report to include deleted materials. Posting here for posterity.

SELECT 
    fy.FiscalYearID,
    fy.Name AS FiscalYearName,
    fu.FundID,
    fu.Name + N': ' + fu.AlternativeName AS FundName,
    mt.MaterialTypeID,
    mt.Description AS MaterialType,
    SUM(seg2.quantrec) AS nItems,
    CAST(SUM(ISNULL(lin.DiscPriceUnitBase, 0.00)) AS MONEY) AS Total,
    CAST(SUM(ISNULL(lin.DiscPriceUnitBase, 0.00)) AS MONEY) / SUM(seg2.quantrec) AS AvgCostPerItem
FROM polaris.polaris.FiscalYears fy WITH (NOLOCK)
INNER JOIN polaris.polaris.Funds fu WITH (NOLOCK)
    ON fy.FiscalYearID = fu.FiscalYearID
INNER JOIN polaris.polaris.InvLineItemSegmentAmts seg WITH (NOLOCK)
    ON fy.FiscalYearID = seg.FiscalYearID AND seg.FundID = fu.FundID
INNER JOIN polaris.polaris.InvLineItemSegments seg2
    ON seg.InvLineItemSegmentID = seg2.InvLineItemSegmentID
INNER JOIN polaris.polaris.InvLines lin
    ON seg2.InvLineItemID = lin.InvLineItemID
INNER JOIN polaris.polaris.MaterialTypes mt WITH (NOLOCK)
    ON lin.MaterialTypeID = mt.MaterialTypeID
WHERE seg2.PaymentStatusID IN (7, 9)

GROUP BY 
    fy.FiscalYearID,
    fy.Name,
    fu.FundID,
    fu.Name + N': ' + fu.AlternativeName,
    mt.MaterialTypeID,
    mt.Description
ORDER BY 
    fy.Name,
    fu.Name + N': ' + fu.AlternativeName,
    mt.Description;
1 Like