USP_DATALIST_DISCOUNT

CREATE procedure dbo.USP_DATALIST_DISCOUNT
(
    @INCLUDEINACTIVE bit = 1
)
as
    set nocount on;

    with VALUES_CTE as
    (
        select
            DISCOUNT.ID,
            case DISCOUNT.APPLIESTOCODE
                when 1 then -- When item-level discount

                    case DISCOUNT.CALCULATIONTYPECODE
                        when 1 then --percent

                            case DISCOUNT.DISCOUNTTYPECODE
                                when 2 then
                                    GROUPSIZEDISCOUNT.[PERCENT]
                                else
                                     DISCOUNTPRICETYPE.[PERCENT]
                            end
                        else  --amount or specific value

                            case DISCOUNT.DISCOUNTTYPECODE
                                when 2 then
                                    cast(GROUPSIZEDISCOUNT.AMOUNT as decimal(8,2))
                                else
                                    cast(DISCOUNTPRICETYPE.AMOUNT as decimal(8,2))
                            end
                        end
                else --When order-level discount

                    case CALCULATIONTYPECODE
                        when 0 then cast(DISCOUNT.AMOUNT as decimal(8,2))
                        else DISCOUNT.[PERCENT]
                    end
            end VALUE
        from dbo.DISCOUNT
        left join dbo.GROUPSIZEDISCOUNT on (DISCOUNT.DISCOUNTTYPECODE = 2 and GROUPSIZEDISCOUNT.DISCOUNTID = DISCOUNT.ID)
        left join dbo.DISCOUNTPRICETYPE on (DISCOUNT.DISCOUNTTYPECODE <> 2 and DISCOUNTPRICETYPE.DISCOUNTID = DISCOUNT.ID)
        where DISCOUNT.SUPERSEDEDBYID is null
            and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)

        union all

        select ID, cast(MERCHANDISEAMOUNT as decimal(8,2))
        from dbo.DISCOUNT
        where DISCOUNT.SUPERSEDEDBYID is null
            and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)

        union all

        select ID, MERCHANDISEPERCENT
        from dbo.DISCOUNT
        where DISCOUNT.SUPERSEDEDBYID is null
            and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)
    ),
    RANKEDVALUES_CTE as
    (
        select
            ID,
            cast(VALUE as nvarchar(20)) VALUE,
            row_number() over (partition by ID order by VALUE desc) MAXRANK,
            row_number() over (partition by ID order by VALUE asc) MINRANK
        from VALUES_CTE
        where VALUE <> 0
    )
    select
        DISCOUNT.ID,
        NAME,
        DISCOUNTTYPE,
        APPLIESTO,
        APPLICATIONTYPE,
        CALCULATIONTYPE,
        case
            when MINVALUES.VALUE = MAXVALUES.VALUE then MINVALUES.VALUE
            else MINVALUES.VALUE + ' - ' + MAXVALUES.VALUE
        end VALUE,
        ISACTIVE,
        DESCRIPTION
    from dbo.DISCOUNT
    left join RANKEDVALUES_CTE as MINVALUES on (MINVALUES.ID = DISCOUNT.ID and MINVALUES.MINRANK = 1)
    left join RANKEDVALUES_CTE as MAXVALUES on (MAXVALUES.ID = DISCOUNT.ID and MAXVALUES.MAXRANK = 1)
    where DISCOUNT.SUPERSEDEDBYID is null
        and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)
    order by NAME;

    return 0;