USP_DATALIST_RATESCALEPRICE

Lists all group sizes and prices for a Rate Scale.
Parameters

Parameter	Parameter Type	Mode	Description
@CONTEXTID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


            CREATE procedure dbo.USP_DATALIST_RATESCALEPRICE
            (
                @CONTEXTID uniqueidentifier
            )
            as
                set nocount on;

                select
                    RATESCALEPRICE.ID,
                    case
                        when GROUPMINIMUM = 0 and GROUPMAXIMUM = -1 then
                            'Any size'
                        when GROUPMINIMUM = 0 and GROUPMAXIMUM <> -1 then
                            cast(GROUPMAXIMUM as nvarchar(6)) + ' or fewer'
                        when GROUPMINIMUM <> 0 and GROUPMAXIMUM = -1 Then
                            cast(GROUPMINIMUM as nvarchar(6)) + ' or more'
                        when GROUPMINIMUM = GROUPMAXIMUM then
                            cast(GROUPMINIMUM as nvarchar(6))
                        else
                            cast(GROUPMINIMUM as nvarchar(6)) + ' - ' + cast(GROUPMAXIMUM as nvarchar(6))
                        end as SIZE,
                    case
                        when USEPERTICKETAFTERMAX = 1 and GROUPMAXIMUM = -1 then null
                        else AMOUNT
                    end as AMOUNT,
                    case
                        when USEPERTICKETAFTERMAX = 1 and GROUPMAXIMUM = -1 then cast(AMOUNT as nvarchar(9)) + ' plus per ticket after ' + cast((GROUPMINIMUM - 1) as nvarchar(3))
                        else cast(AMOUNT as nvarchar(35))
                    end as FORMATTEDAMOUNT
                from dbo.RATESCALEPRICE
                    inner join dbo.RATESCALE on RATESCALE.ID = RATESCALEPRICE.RATESCALEID
                where RATESCALE.ID = @CONTEXTID
                order by GROUPMINIMUM