UFN_BILLINGITEM_VALIDFORSPECIFIEDDATE

CREATE function dbo.UFN_BILLINGITEM_VALIDFORSPECIFIEDDATE
    (
        @ID uniqueidentifier,
        @SPECIFIEDDATE date
    )
returns bit
as begin
    -- do work here and return a value

    declare @retval int;
    declare @type tinyint;

    set @retval = 0;

    select @type=PRICETYPECODE from dbo.BILLINGITEM where BILLINGITEM.ID=@ID

    if @type=1
    begin
    if (exists (select BIP.ID
         from dbo.BILLINGITEMPRICE BIP
                 where BIP.BILLINGITEMID = @ID and
                 (BIP.DATERANGETYPECODE=0 or (BIP.STARTDATE<=@SPECIFIEDDATE and (BIP.ENDDATE>=@SPECIFIEDDATE or BIP.ENDDATE is null)))))
        set @retval = 1;
    end

    if @type=2 or @type=3
    begin
    if (exists (select BID.ID
         from dbo.BILLINGITEMDATES BID
                 where BID.BILLINGITEMID = @ID and
                 (BID.DATERANGETYPECODE=0 or (BID.STARTDATE<=@SPECIFIEDDATE and (BID.ENDDATE>=@SPECIFIEDDATE or BID.ENDDATE is null)))))
        set @retval = 1;    
    end    

    return @retval;
end