UFN_REPORT_DASHBOARD_DAILYADMISSIONCOUNT_BASE

create function dbo.UFN_REPORT_DASHBOARD_DAILYADMISSIONCOUNT_BASE
(
    @FROMDATE date = null,
    @TODATE datetime = null
)
returns table
as return
    select
        coalesce(RESERVATION.ARRIVALDATE, FINANCIALTRANSACTION.CALCULATEDDATE, cast(SALESORDER.TRANSACTIONDATE as date)) TransactionDate,
        PRICETYPECODE.[DESCRIPTION] PriceTypeDescription,
        coalesce(sum(SALESORDERITEM.QUANTITY), 0) - sum(CANCELLEDTICKETS.QUANTITY) AdmissionCount
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join dbo.PRICETYPECODE on SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
    inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    left join dbo.RESERVATION on SALESORDER.ID = RESERVATION.ID
    left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
    outer apply (
        select count(*) as QUANTITY from dbo.TICKET where SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID and STATUSCODE = 2  -- Cancelled

    ) as CANCELLEDTICKETS
    where (SALESORDER.STATUSCODE = 1 or (SALESORDER.SALESMETHODTYPECODE = 3 and SALESORDER.STATUSCODE <> 5))
        and SALESORDERITEMTICKET.EVENTID is null
        and coalesce(RESERVATION.ARRIVALDATE, FINANCIALTRANSACTION.CALCULATEDDATE, SALESORDER.TRANSACTIONDATE) between @FROMDATE and dbo.UFN_DATE_GETLATESTTIME(@TODATE)
    group by PRICETYPECODE.[DESCRIPTION], coalesce(RESERVATION.ARRIVALDATE, FINANCIALTRANSACTION.CALCULATEDDATE, cast(SALESORDER.TRANSACTIONDATE as date));