USP_REPORT_DAILYRESERVATION

create procedure dbo.USP_REPORT_DAILYRESERVATION
(
    @FROMDATE datetime = null, 
    @TODATE datetime = null
)
as
    set nocount on;

    set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE) 
    set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE)

    select
        'http://www.blackbaud.com/RESERVATIONID?RESERVATIONID=' + CONVERT(nvarchar(36), R.ID) RESERVATIONID,
        case 
            when len(R.ARRIVALTIME) = 0 then
                R.STARTDATETIME
            else
                dbo.UFN_DATE_ADDHOURMINUTE(R.ARRIVALDATE, R.ARRIVALTIME)
        end RESERVATIONDATETIME,
        R.NAME RESERVATIONNAME,
        dbo.UFN_RESERVATION_TOTALVISITORCOUNT(R.ID) TOTALVISITORS,
        CONTACTNAMEFORMAT.NAME as CONTACTNAME,
        coalesce(P.NUMBERNOFORMAT,'') CONTACTPHONE,
        R.NUMBEROFBUSES NUMBEROFBUSES,
        R.ARRIVALNOTES NOTES,
        R.SECURITYDEPOSITREQUIRED,
        R.SECURITYDEPOSITSTATUS,
        (select sum(AMOUNT) from dbo.RESERVATIONSECURITYDEPOSITPAYMENT where RESERVATIONSECURITYDEPOSITPAYMENT.RESERVATIONID = R.ID) as SECURITYDEPOSITPAID
    from
        dbo.RESERVATION R
    inner join
        dbo.SALESORDER SO on SO.ID = R.ID
    outer apply
        dbo.UFN_CONSTITUENT_DISPLAYNAME(SO.RECIPIENTID) as CONTACTNAMEFORMAT
    outer apply 
        dbo.UFN_SALESORDER_CONTACTRECORDS(R.ID) as CONTACTRECORDS
    left outer join
        dbo.PHONE P on P.ID = CONTACTRECORDS.PHONEID
    where
        (@FROMDATE is null or R.ARRIVALDATE >= @FROMDATE)
        and (@TODATE is null or R.ARRIVALDATE <= @TODATE)
        and SO.STATUSCODE <> 5  -- Cancelled