USP_DATALIST_PROGRAMDISCOUNT

CREATE procedure dbo.USP_DATALIST_PROGRAMDISCOUNT
(
    @PROGRAMID uniqueidentifier,
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;

    with VALUES_CTE as
    (
        select distinct
            [D].[ID],
            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
                                    (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] asc)
                                else
                                    (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 
                            convert(nvarchar(20), [D].[AMOUNT])
                        else
                            convert(nvarchar(20), [D].[PERCENT])
                    end
            end 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] 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
                                    (select top (1) convert(nvarchar(20), [AMOUNT]) from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] desc)
                                else
                                    (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 
                            convert(nvarchar(20), [D].[AMOUNT])
                        else
                            convert(nvarchar(20), [D].[PERCENT])
                    end
            end MAXVALUE
        from dbo.[DISCOUNT] [D]
        left join dbo.[DISCOUNTPRICETYPE] on [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
    )
    select
        distinct D.ID,
        DG.ID as DISCOUNTGROUPID,
        D.NAME,
        D.DISCOUNTTYPE,
        D.APPLIESTO,
        D.APPLICATIONTYPE,
        D.CALCULATIONTYPE,
        case
            when [MINVALUE] = [MAXVALUE] then
                [MINVALUE]
            else
                [MINVALUE] + ' - ' + [MAXVALUE]
        end [VALUE],
        ISACTIVE,
        D.[DESCRIPTION]
    from dbo.DISCOUNT D
        inner join dbo.DISCOUNTGROUP DG on D.ID = DG.DISCOUNTID
        inner join dbo.DISCOUNTGROUPDETAIL DGD on DG.ID = DGD.DISCOUNTGROUPID
        inner join dbo.DISCOUNTGROUPDETAILPROGRAM DGDP on DGD.ID = DGDP.ID
        inner join [VALUES_CTE] as [VALUES] on [VALUES].[ID] = [D].[ID]
    where
        DGDP.PROGRAMID = @PROGRAMID and
        D.SUPERSEDEDBYID is null and
        (@INCLUDEINACTIVE = 1 or D.ISACTIVE = 1)
    order by NAME;

    return 0;