USP_DATALIST_DISCOUNTPROPERTIES
Returns the value and calculation type for a discount.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DISCOUNTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_DISCOUNTPROPERTIES
(
@DISCOUNTID uniqueidentifier = null
)
as
select
[D].[ID],
0 as [VALUE], -- Use min and max value now
[D].[CALCULATIONTYPECODE],
[D].[DISCOUNTTYPECODE],
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) [PERCENT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [PERCENT] asc)
else
(select top (1) [PERCENT]
from
(select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
union all
select [D].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
where [PERCENT] > 0
order by [PERCENT] asc)
end
else --amount or specific value
case [D].[DISCOUNTTYPECODE]
when 2 then
(select top (1) [AMOUNT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] asc)
else
(select top (1) [AMOUNT]
from
(select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
union all
select [D].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
where [AMOUNT] > 0
order by [AMOUNT] asc)
end
end
else --When order-level discount
case [CALCULATIONTYPECODE]
when 0 then
[D].[AMOUNT]
else
[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) [PERCENT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [PERCENT] desc)
else
(select top (1) [PERCENT]
from
(select [PERCENT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
union all
select [D].[MERCHANDISEPERCENT] as [PERCENT]) as [PERCENTS]
order by [PERCENT] desc)
end
else --amount or specific value
case [D].[DISCOUNTTYPECODE]
when 2 then
(select top (1) [AMOUNT] from dbo.[GROUPSIZEDISCOUNT] where [GROUPSIZEDISCOUNT].[DISCOUNTID] = [D].[ID] order by [AMOUNT] desc)
else
(select top (1) [AMOUNT]
from
(select [AMOUNT] from dbo.[DISCOUNTPRICETYPE] where [DISCOUNTPRICETYPE].[DISCOUNTID] = [D].[ID]
union all
select [D].[MERCHANDISEAMOUNT] as [AMOUNT]) as [AMOUNTS]
order by [AMOUNT] desc)
end
end
else --When order-level discount
case [CALCULATIONTYPECODE]
when 0 then
[D].[AMOUNT]
else
[D].[PERCENT]
end
end MAXVALUE
from dbo.[DISCOUNT] D
where [ID] = @DISCOUNTID