UFN_EVENTLODGING_GETROOMCOUNTS

CREATE function dbo.UFN_EVENTLODGING_GETROOMCOUNTS
(
    @EVENTLODGINGID uniqueidentifier
)
returns table
as
    return
    (
        with TOTALS(EVENTLODGINGROOMID,ASSIGNED)
        as
        (
            select
                EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID as EVENTLODGINGROOMID,
                COUNT(EVENTLODGINGROOMINSTANCE.ID) as ASSIGNED
            from
                dbo.EVENTLODGINGROOMINSTANCE
            where
                EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = @EVENTLODGINGID
                and EVENTLODGINGROOMINSTANCE.ID in (select EVENTLODGINGROOMINSTANCEID from dbo.REGISTRANTLODGING)
            group by
                EVENTLODGINGROOMID
        )
        select
            EVENTLODGINGROOM.ID,
            EVENTLODGINGROOMTYPECODE.DESCRIPTION as ROOMTYPE,
            EVENTLODGINGROOM.ALLOCATED as ALLOCATED,
            coalesce(TOTALS.ASSIGNED, 0) as ASSIGNED,
            case when EVENTLODGINGROOM.ALLOCATED < coalesce(TOTALS.ASSIGNED, 0) then 0
                else EVENTLODGINGROOM.ALLOCATED - coalesce(TOTALS.ASSIGNED, 0) 
                end as REMAINING,
            case when coalesce(TOTALS.ASSIGNED, 0) < EVENTLODGINGROOM.ALLOCATED then 0
                else coalesce(TOTALS.ASSIGNED, 0) - EVENTLODGINGROOM.ALLOCATED
                end as OVERASSIGNED
        from
            dbo.EVENTLODGINGROOM
        inner join    
            dbo.EVENTLODGINGROOMTYPECODE on EVENTLODGINGROOMTYPECODE.ID = EVENTLODGINGROOM.ROOMTYPECODEID
        left join
            TOTALS on TOTALS.EVENTLODGINGROOMID = EVENTLODGINGROOM.ID
        where 
            EVENTLODGINGROOM.EVENTLODGINGID = @EVENTLODGINGID
    )