create FUNCTION [dbo].[udf_SelectSTDCOST_TVF] 
(
    @ToDate datetime
)
RETURNS TABLE
AS
RETURN
(
    WITH StockStart AS (
        SELECT STOCKDATE FROM STOCKSTARTDATE
    ),
    Purchase AS (
        SELECT ItemCode,
               PurchaseCost = SUM(RateAfterexp * Qty),
               PurchaseStock = SUM(Qty * QTYPERUNIT)
        FROM PurchaseItem, StockStart
        WHERE PR = 'Purchase' 
          AND EntryDate BETWEEN STOCKDATE AND @ToDate
        GROUP BY ItemCode
    )
	,
    Material AS (
        SELECT ItemCode,
               MaterialInCost = SUM(RATE * Qty),
               MaterialInStock = SUM(Qty * QTYPERUNIT)
        FROM MATERIALTRANSDETAILS, StockStart
        WHERE EntryDate BETWEEN STOCKDATE AND @ToDate
        GROUP BY ItemCode
    ),
    FinalProd AS (
        SELECT p.ItemCode,
               FinalProductCost = SUM(COST * Qty),
               FinalProductStock = SUM(Qty * QTYPERUNIT)
        FROM ProductionMasterItemFP p
        INNER JOIN ProductionEntryMaster m ON m.DOCNO = p.DOCNO
        CROSS JOIN StockStart
        WHERE m.EntryDate BETWEEN STOCKDATE AND @ToDate
        GROUP BY p.ItemCode
    )
    SELECT 
        COALESCE(p.ItemCode, m.ItemCode, f.ItemCode, o.ItemCode) AS ItemCode,
        STDCOST = 
            CASE 
                WHEN ISNULL(OpenQty, 0) + ISNULL(PurchaseStock, 0) + ISNULL(MaterialInStock, 0) + ISNULL(FinalProductStock, 0) = 0 THEN 0
                ELSE ROUND((
                    ISNULL(OpenQty * OpenRate, 0) +
                    ISNULL(PurchaseCost, 0) +
                    ISNULL(MaterialInCost, 0) +
                    ISNULL(FinalProductCost, 0)
                ) / NULLIF(
                    ISNULL(OpenQty, 0) +
                    ISNULL(PurchaseStock, 0) +
                    ISNULL(MaterialInStock, 0) +
                    ISNULL(FinalProductStock, 0)
                , 0), 2)
            END
    FROM
        (SELECT ItemCode, Quantity AS OpenQty, RATE AS OpenRate FROM OpeningStockEntryItemForupdate) o
        FULL OUTER JOIN Purchase p ON o.ItemCode = p.ItemCode
        FULL OUTER JOIN Material m ON COALESCE(o.ItemCode, p.ItemCode) = m.ItemCode
        FULL OUTER JOIN FinalProd f ON COALESCE(o.ItemCode, p.ItemCode, m.ItemCode) = f.ItemCode
);