USP_DATALIST_EVENTPROFILEREPORT_LODGINGLOCATIONS

Returns lodging locations for an event.
Parameters

Parameter	Parameter Type	Mode	Description
@EVENTID	uniqueidentifier	IN	Event ID
@ISVISIBLE	bit	IN	Visible
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
Definition

Copy


        CREATE procedure dbo.USP_DATALIST_EVENTPROFILEREPORT_LODGINGLOCATIONS
          (
                      @EVENTID uniqueidentifier,
                      @ISVISIBLE bit = 1,
                      @CURRENTAPPUSERID uniqueidentifier
                    )

          as
          set nocount on;

          if @ISVISIBLE = 1
            begin
            declare @TOTALS table
                  (
                      EVENTLODGINGID uniqueidentifier,
                      ALLOCATED int,
                      ASSIGNED int
                  );

                  insert into @TOTALS
                  (
                      EVENTLODGINGID,
                      ALLOCATED,
                      ASSIGNED
                  )
                  (
                      select
                          EVENTLODGING.ID as EVENTLODGINGID,
                          (select SUM(EVENTLODGINGROOM.ALLOCATED) from EVENTLODGINGROOM where EVENTLODGINGROOM.EVENTLODGINGID = EVENTLODGING.ID) as ALLOCATED,
                          (select 
                              COUNT(EVENTLODGINGROOMINSTANCE.ID)
                          from
                              EVENTLODGINGROOMINSTANCE 
                          where 
                              EVENTLODGINGROOMINSTANCE.EVENTLODGINGID = EVENTLODGING.ID
                              -- Only count rooms with occupants

                              and EVENTLODGINGROOMINSTANCE.ID in (select EVENTLODGINGROOMINSTANCEID from dbo.REGISTRANTLODGING)
                          ) as ASSIGNED
                      from
                          dbo.EVENTLODGING
                      where 
                          EVENTLODGING.EVENTID = @EVENTID
                  );

                 select 
                      EVENTLODGINGLOCATION.NAME,
                      TOTALS.ALLOCATED as TOTALALLOCATED,
                      TOTALS.ASSIGNED as TOTALASSIGNED,
                      case when TOTALS.ALLOCATED < TOTALS.ASSIGNED then 0
                          else TOTALS.ALLOCATED - TOTALS.ASSIGNED 
                          end as TOTALREMAINING,
                      case when TOTALS.ASSIGNED < TOTALS.ALLOCATED then 0
                          else TOTALS.ASSIGNED - TOTALS.ALLOCATED
                          end as TOTALOVERASSIGNED
                  from 
                      dbo.EVENTLODGING
                  inner join 
                      dbo.EVENTLODGINGLOCATION on EVENTLODGINGLOCATION.ID = EVENTLODGING.EVENTLODGINGLOCATIONID
                  inner join
                      @TOTALS as TOTALS on TOTALS.EVENTLODGINGID = EVENTLODGING.ID
                  where 
                      EVENTLODGING.EVENTID = @EVENTID
            end