UFN_DISCOUNT_GENERATEVALUEDESCRIPTION

            CREATE function dbo.UFN_DISCOUNT_GENERATEVALUEDESCRIPTION(@DISCOUNTID uniqueidentifier)
            returns nvarchar(40)
            with execute as caller
            as begin
                declare @DESCRIPTION nvarchar(40)
                declare @VALUE nvarchar(20)
                declare @MINVALUE nvarchar(20)
                declare @MAXVALUE nvarchar(20)
                declare @CURRENCYSYMBOL nchar(1) = '$'


                select
                    @MINVALUE =
                        case [D].[APPLIESTOCODE]
                            when 1 then -- When item-level discount

                                case [D].[CALCULATIONTYPECODE]
                                    when 1 then --percent

                                        case [D].[DISCOUNTTYPECODE]
                                            when 2 then
                                                (select top (1) convert(nvarchar(20), [PERCENT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [PERCENT] asc) + '%'
                                            else
                                                (select top (1) convert(nvarchar(20), [PERCENT]) from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID] order by [PERCENT] asc) + '%'
                                        end
                                    else  --amount or specific value

                                        case [D].[DISCOUNTTYPECODE]
                                            when 2 then
                                                @CURRENCYSYMBOL + (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] asc)
                                            else
                                                @CURRENCYSYMBOL + (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID] order by [AMOUNT] asc)
                                        end
                                    end
                            else --When order-level discount

                                case [CALCULATIONTYPECODE]
                                    when 0 then 
                                        @CURRENCYSYMBOL + convert(nvarchar(20), [D].[AMOUNT])
                                    else
                                        convert(nvarchar(20), [D].[PERCENT]) + '%'
                                end
                        end,
                    @MAXVALUE = 
                        case [D].[APPLIESTOCODE]
                            when 1 then  -- When item-level discount

                                case [D].[CALCULATIONTYPECODE]
                                    when 1 then --percent

                                        case [D].[DISCOUNTTYPECODE]
                                            when 2 then
                                                (select top (1) convert(nvarchar(20), [PERCENT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [PERCENT] desc) + '%'
                                            else
                                                (select top (1) convert(nvarchar(20), [PERCENT]) from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID] order by [PERCENT] desc) + '%'
                                        end
                                    else --amount or specific value

                                        case [D].[DISCOUNTTYPECODE]
                                            when 2 then
                                                @CURRENCYSYMBOL + (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] desc)
                                            else
                                                @CURRENCYSYMBOL + (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID] order by [AMOUNT] desc)
                                        end
                                end
                            else --When order-level discount

                                case [CALCULATIONTYPECODE]
                                    when 0 then 
                                        @CURRENCYSYMBOL + convert(nvarchar(20), [D].[AMOUNT])
                                    else
                                        convert(nvarchar(20), [D].[PERCENT]) + '%'
                                end
                        end
                from dbo.[DISCOUNT] [D]
                where [D].[ID] = @DISCOUNTID

                if @MINVALUE = @MAXVALUE
                    set @VALUE = @MINVALUE
                else
                    set @VALUE = @MINVALUE + ' - ' + @MAXVALUE

                select
                    @DESCRIPTION = case DISCOUNTTYPECODE
                        when 1 then 'Purchase ' + convert(nvarchar, NUMBERTOPURCHASE) + ' discount on '  + convert(nvarchar, NUMBERTODISCOUNT)
                        else
                            case [CALCULATIONTYPECODE]
                                when 2 then
                                    @VALUE + ' per item'
                                else
                                    @VALUE + ' off per ' + lower([APPLIESTO])
                            end
                    end
                from dbo.DISCOUNT
                where ID = @DISCOUNTID

                return @DESCRIPTION
            end