The decision table for discount process

Overview

In the context of Point of sales (POS) for many industry, it is quite common that a shop might run promotion on different period, weekday and whether holiday should give discount or not. The discount process that we are discussing in this article is an automated process. Meaning that the cashier will scan the barcode of the item and the discount will be given automatically.

To run a complex price discount process based on the date factor, the decision table will look like this:

Rule #	Is holiday	Day of week	Exclude holiday	Include holidy	Give discount?
1	n	y	n	n	y
2	n	n	n	n	n
3	y	y	n	n	y
4	y	y	y	n	n
5	y	y	n	y	y
6	y	n	n	n	n
7	y	n	y	n	n
8	y	n	n	y	y

The decision table explanations

• 'Is holiday' is a table that stores the date of holiday. This table is to be setup by the system admin.
• 'Day of week', 'exclude holiday' and 'include holiday' is a boolean (checkbox field on the scren) and the setting should be kept in the 'price discount' table. This table is to be setup by the system admin as well.
• 'Day of week' which has 7 fields that includes Monday, Tuesday, etc. 'y', means that week day has discount.
• 'Exclude holiday' is a field that is used in conjunction with 'day of week'.
• 'Include holiday' is a field that is used in conjunction with 'day of week' and it is mutually exclusive from 'exclude holiday'.

Use case

• Discount is applicable for weekdays (Monday to Friday) regardless holiday - rule #1 and #3 applies.
• Discount is applicable for weekdays (Monday to Friday) excluding holiday - rule #4 applies.
• Discount is applicable for weekdays (Monday to Friday) and applicable holiday - rule #5 applies.
• Discount is not applicable for weekdays (Monday to Friday) regardless holiday- rule #2 and #6 applies.
• Discount is not applicable for weekdays (Monday to Friday) and applicable on holiday - rule #8 applies.
• Rule #7 is invalid. So, we may ingore it.

Table structure

We need three tables to store the master setup. One for the holiday, one for the product and another one for the discount. It's a one to many relationship between tb_prod and tb_discount.

create table tb_holiday (
    dt datetime not null
);

create table tb_prod (
    prod_id int not null
    , price money not null
);

create table tb_discount (
    prod_id int not null        
    , start_dt datetime null
    , end_dt datetime null
    , discount_pct numeric(6,2) null
    , for_mon int null
    , for_tue int null
    , for_wed int null
    , for_thu int null
    , for_fri int null
    , for_sat int null
    , for_sun int null
    , exclude_holiday int null
    , include_holiday int null
)

Setting up the test data

delete from tb_holiday;
delete from tb_prod;
delete from tb_discount;

insert into tb_holiday (dt) values 
('2021-01-13');

insert into tb_prod (prod_id, price) values
(1, 100)
, (2, 200);

insert into tb_discount (prod_id, start_dt, end_dt, discount_pct
, for_mon, for_tue, for_wed, for_thu, for_fri, for_sat, for_sun
, exclude_holiday, include_holiday) values 
(1, '2021-01-01', '2021-01-31', 0.1
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-02-01', '2021-02-28', 0.15
    , 1, 1, 1, 1, 1, 0, 0
    , 1, 0)
, (1, '2021-01-01', '2021-01-31', 0.05
    , 0, 0, 0, 0, 0, 1, 1
    , 0, 1)
, (1, '2021-01-01', '2021-01-31', 0.03
	, 0, 0, 0, 0, 0, 1, 1
	, 0, 1);    

To query the discount for any product

declare
    @is_holiday int 
    , @dow int 
    , @curr_dt datetime 
    , @prod_id int

set @prod_id = 1
set @curr_dt = '2021-01-11'
set @dow = datepart(weekday, @curr_dt)

if exists(
    select *	
    from tb_holiday
    where dt = @curr_dt
)
begin
    set @is_holiday = 1
end
else
begin
    set @is_holiday = 0
end

select 
    discount_pct
from tb_discount d
where
    prod_id = @prod_id
    and @curr_dt between d.start_dt and d.end_dt
    and (			
        (@is_holiday = 1 and (d.include_holiday = 1 and d.exclude_holiday = 0))
        or ((
                   (@dow = 1 and d.for_sun = 1)
                or (@dow = 2 and d.for_mon = 1)
                or (@dow = 3 and d.for_tue = 1)
                or (@dow = 4 and d.for_wed = 1)
                or (@dow = 5 and d.for_thu = 1)
                or (@dow = 6 and d.for_fri = 1)
                or (@dow = 7 and d.for_sat = 1)
            ) 
            and (
                (@is_holiday = 0)
                or (@is_holiday = 1 
                    and (d.include_holiday = 1 or d.exclude_holiday = 0)
                )
        ))
    )

• The above query first is to identify the day of week and check to see if it is holiday.

• Follow by filtering the discount record by prod ID and the period.

• It then checks the day of week and holiday.

• Finally, returns the discount percentage.

• Test case (please change the value of @curr_dt in the query)

  • For @curr_dt = 2021-01-11 (Monday), the discount percentage is 10%. The week day discount in Jan 2021.
  • For @curr_dt = 2021-01-13 (Wednesday, holiday), the discount percentage is 3%.
  • For @curr_dt = 2021-01-16 (Saturday), the discount percentage is 5%. There is a caveat in the result because the query returns two discount records.
  • For @curr_dt = 2021-02-11 (Thursday), the discount percentage is 15%. The week day discount in Feb 2021.

• Caveat - there might be more than one discount record returned by the query. To avoid this from happening,

  1. During the discount setup time, you may have to prompt the user on the conflict OR
  2. In case the conflicting discount allows to be saved, you may have to return the latest discount record using TOP 1 + ORDER BY modified_on or any other condition that will force the query returning only one discount record.