USP_REPORT_DASHBOARD_DAILYADMISSIONCOUNT_BYDATE

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

    declare @ADMISSIONDATES table(
        TransactionDate date,
        AdmissionCount int
    );

    insert into @ADMISSIONDATES
    select TransactionDate, sum(AdmissionCount)
    from dbo.UFN_REPORT_DASHBOARD_DAILYADMISSIONCOUNT_BASE(@FROMDATE, @TODATE)
    group by TransactionDate;

    -- Trim the range to exclude leading and trailing zeroes.

    declare @MINDATE date, @MAXDATE date;
    select
        @MINDATE = coalesce(min(TransactionDate), @FROMDATE),
        @MAXDATE = coalesce(max(TransactionDate), @TODATE)
    from @ADMISSIONDATES;

    -- Add zeroes for dates inside the range on which no orders were sold.

    select
        DATES.[DATE] TransactionDate,
        coalesce(AdmissionCount, 0) AdmissionCount
    from dbo.UFN_CALENDARDATES(@MINDATE, @MAXDATE, 1) DATES
    left join @ADMISSIONDATES ADMISSION on DATES.[DATE] = ADMISSION.TransactionDate
    order by [DATE];