SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER FUNCTION dbo.GetProjectByAll
(
    @date DATETIME,
    @Item NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @Proj_num NVARCHAR(20);
    DECLARE @flag INT;


    SELECT @flag = COUNT(co.co_num)
    FROM dbo.coitem_mst coi
        JOIN dbo.co_mst co
            ON co.co_num = coi.co_num
               AND coi.site_ref = co.site_ref
    WHERE CAST(@date AS DATE) >= CAST(co.order_date AS DATE)
          AND @Item = coi.item
    GROUP BY co.Uf_ProjNum;
    IF (@flag = 1)
    BEGIN

        SELECT @Proj_num = co.Uf_ProjNum
        FROM dbo.coitem_mst coi
            JOIN dbo.co_mst co
                ON co.co_num = coi.co_num
                   AND coi.site_ref = co.site_ref
        WHERE CAST(@date AS DATE) >= CAST(co.order_date AS DATE)
              AND @Item = coi.item
        GROUP BY co.Uf_ProjNum;
    --   RETURN @Proj_num;
    END;
    ELSE
    BEGIN

        SELECT @Proj_num = Uf_ProjNum
        FROM
        (
            SELECT co.Uf_ProjNum,
                   coi.item,
                   MAX(co.order_date) order_date
            FROM dbo.coitem_mst coi
                JOIN dbo.co_mst co
                    ON co.co_num = coi.co_num
                       AND coi.site_ref = co.site_ref
            WHERE CAST(@date AS DATE) >= CAST(co.order_date AS DATE)
            GROUP BY coi.item,
                     co.Uf_ProjNum
        ) a;
    -- HAVING COUNT(co.co_num) > 1;
    -- RETURN @Proj_num;
    END;
    RETURN @Proj_num;
END;
GO