tsql-pr1

--SELECT * FROM Invoice ii JOin ProductInvoice pii ON ii.idInvoice=pii.idInvoice
--SELECT * FROM CostHistory
--SELECT * FROM Invoice ii
--SELECT * FROM Product p

--CREATE FUNCTION func_getProductCost_FromHistory(@p_date DATE) RETURNS FLOAT
ALTER FUNCTION func_getProductCost_FromHistory(@p_productID int, @p_date DATE) RETURNS FLOAT
AS
BEGIN
    DECLARE @v_result FLOAT;
    DECLARE @v_cost FLOAT;

    SET @v_result = -1;
    DECLARE ccc CURSOR FOR (SELECT ch.cost as CENA FROM CostHistory ch WHERE ch.idProduct=@p_productID AND (@p_date BETWEEN ch."from" AND ch."to"));
    OPEN ccc;
    FETCH NEXT FROM ccc INTO @v_cost;
    WHILE(@@FETCH_STATUS=0)BEGIN
        SET @v_result = @v_cost;

        FETCH NEXT FROM ccc INTO @v_cost;
    END;
    CLOSE ccc;
    DEALLOCATE ccc;

    RETURN @v_result;
END;

--CREATE FUNCTION func_haveProductCostHistory(@p_productID int) RETURNS BIT
ALTER FUNCTION func_haveProductCostHistory(@p_productID int) RETURNS BIT
AS
BEGIN
    DECLARE @v_count INT;
    SELECT @v_count = COUNT(*) FROM CostHistory ch WHERE ch.idProduct=@p_productID;

    if(@v_count>0)BEGIN
        RETURN 1;
    END;

    RETURN 0;
END;

BEGIN
    DECLARE @v_invoceID INT;
    DECLARE @v_PRICE FLOAT;
    DECLARE @v_productPrice FLOAT;
    DECLARE @v_productID INT;
    DECLARE @v_invoceDate DATE;
    DECLARE @v_haveProductCostHistory BIT;
    DECLARE @v_amount INT;

    SET @v_PRICE = 0;


    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    SET @v_invoceID = 2;
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!
    --ID OBJEDNÁVKY!!!!!!!!!!!!!!!!!!!!!!!!

    DECLARE ccc CURSOR FOR (SELECT pii.idProduct, ii."timestamp" FROM Invoice ii JOin ProductInvoice pii ON ii.idInvoice=pii.idInvoice WHERE pii.idInvoice=@v_invoceID);
    OPEN ccc;
    FETCH NEXT FROM ccc INTO @v_productID, @v_invoceDate;
    WHILE(@@FETCH_STATUS=0)BEGIN
        SET @v_productPrice = -1;

        SET @v_haveProductCostHistory = dbo.func_haveProductCostHistory(@v_productID);
        IF(@v_haveProductCostHistory=1)BEGIN
            SET @v_productPrice = dbo.func_getProductCost_FromHistory(@v_productID, @v_invoceDate);
        END;
        --PRINT CAST(@v_productID as VARCHAR) + ' - ' + CAST(@v_haveProductCostHistory as VARCHAR);

        IF(@v_productPrice=-1)BEGIN
            SELECT @v_productPrice=p.cost FROM Product p WHERE p.idProduct=@v_productID
        END;


        SELECT @v_amount=prodi.amount FROM ProductInvoice prodi WHERE prodi.idProduct=@v_productID AND prodi.idInvoice=@v_invoceID
        --PRINT @v_productPrice;

        SET @v_PRICE = @v_PRICE+(@v_productPrice*@v_amount);

        FETCH NEXT FROM ccc INTO @v_productID, @v_invoceDate;
    END;
    CLOSE ccc;
    DEALLOCATE ccc;

    PRINT @v_PRICE;
END;