UFN_CONSTITUENT_GETPATRONSTARTDATE

CREATE function dbo.UFN_CONSTITUENT_GETPATRONSTARTDATE(@CONSTITUENTID uniqueidentifier)
returns datetime
as
begin
    declare @STARTDATE datetime = null;

    select top(1)
        @STARTDATE = SALESORDER.TRANSACTIONDATE
    from
        dbo.SALESORDER
    left outer join  -- Left join since group sales may not have sales order items

        dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    left outer join
        dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
    where
        SALESORDER.CONSTITUENTID = @CONSTITUENTID and
        SALESORDER.STATUSCODE <> 0  -- Orders that aren't Pending

        and (
            SALESORDER.SALESMETHODTYPECODE = 3  -- Group Sales

            or SALESORDERITEMTICKET.ID is not null
        )
    order by
        TRANSACTIONDATE asc;

    return @STARTDATE;
end