UFN_DAILYSALEITEM_BUILDDISCOUNTBUTTONTEXT

        CREATE function dbo.UFN_DAILYSALEITEM_BUILDDISCOUNTBUTTONTEXT
        (
            @ID uniqueidentifier, 
            @CURRENCYSYMBOL nchar(1)
        )

        returns nvarchar(65)
        with execute as caller
        as begin
            declare @LINESEPARATOR nchar(1) = char(10)

            declare @DESCRIPTIONTYPECODE1 tinyint
            declare @DESCRIPTIONTYPECODE2 tinyint
            declare @DESCRIPTIONTYPECODE3 tinyint

            declare @DESCRIPTION1 nvarchar(20)
            declare @DESCRIPTION2 nvarchar(20)
            declare @DESCRIPTION3 nvarchar(20)

            select
                @DESCRIPTIONTYPECODE1 = DESCRIPTIONFIELD1TYPECODE,
                @DESCRIPTIONTYPECODE2 = DESCRIPTIONFIELD2TYPECODE,
                @DESCRIPTIONTYPECODE3 = DESCRIPTIONFIELD3TYPECODE,
                @DESCRIPTION1 = DESCRIPTIONFIELD1,
                @DESCRIPTION2 = DESCRIPTIONFIELD2,
                @DESCRIPTION3 = DESCRIPTIONFIELD3
            from dbo.DAILYSALEITEM
            where ID = @ID

            declare @NAME nvarchar(20)
            declare @VALUE nvarchar(20)
            declare @CALCULATIONTYPE nvarchar(20)
            declare @MINVALUE nvarchar(20)
            declare @MAXVALUE nvarchar(20)

            select
                @NAME = [D].[NAME],
                @CALCULATIONTYPE = [D].[CALCULATIONTYPE],
                @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]
            inner join dbo.[DAILYSALEITEMDISCOUNT] [DSID] on [DSID].[DISCOUNTID] = [D].[ID]
            left join dbo.[DISCOUNTPRICETYPE] on [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
            where [DSID].[ID] = @ID;

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


            select @DESCRIPTION1 = 
                case @DESCRIPTIONTYPECODE1
                    when 1 then @DESCRIPTION1
                    when 8 then @NAME
                    when 9 then @VALUE
                    when 10 then @CALCULATIONTYPE
                end

            select @DESCRIPTION2 = 
                case @DESCRIPTIONTYPECODE2
                    when 1 then @DESCRIPTION2
                    when 8 then @NAME
                    when 9 then @VALUE
                    when 10 then @CALCULATIONTYPE
                end

            select @DESCRIPTION3 = 
                case @DESCRIPTIONTYPECODE3
                    when 1 then @DESCRIPTION3
                    when 8 then @NAME
                    when 9 then @VALUE
                    when 10 then @CALCULATIONTYPE
                end

            return coalesce(@DESCRIPTION1, '') + @LINESEPARATOR + coalesce(@DESCRIPTION2, '') + @LINESEPARATOR + coalesce(@DESCRIPTION3, '')
        end