Implementing discount process in MSSQL

Published on: 12th Jan 2021

Updated on 9th May 2021

Overview

In the context of Point of sales (POS) for many industries, it is quite common that a shop might run promotions on different periods, weekdays and whether holidays should give discounts 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.

User requirements

• Common user requirements

  • Give discount between Monday to Friday.
  • Give discounts on Saturday and Sunday.
  • Give a discount on the selected day of week.

• Something challenging

  • Give discounts on Saturday and Sunday. If any day between Monday to Friday is holiday, give a discount as well.
  • Give discount between Monday to Friday. Don't give a discount if any day between Monday to Friday is holiday.

The decision table

After reviewing the user requirements we come out with a decision table. To run a complex price discount process based on the above requirements, the decision table will look like this:

The decision table explanations

• 'Is holiday' - we need a table to store holidays. This table is to be set up by the system admin.
• 'Day of week', 'exclude holiday' and 'include holiday' is a boolean (checkbox field on the screen) and the setting should be kept in the 'price discount' table. This table is to be set up by the system admin as well.
• 'Day of week' which has 7 fields that includes Monday, Tuesday, etc. '[x]', means the user has enabled the condition.
• 'Exclude holiday' is a field that is used in conjunction with 'day of week' and 'is holiday'.
• 'Include holiday' is a field that is used in conjunction with 'day of week' and 'is holiday'. It is mutually exclusive from 'exclude holiday' (i.e., either 'include holiday' or 'exclude holiday' can be '[x]').

Use case

• Give discount to the selected day regardless holiday - rule #1 and #3 applies.
• No discount to the selected day if it is holiday - rule #4 applies.
• Give discount to the selected day, including holiday - rule #5 applies.
• No discount to the selected day regardless holiday- rule #2 and #6 applies.
• No discount to the selected day in case it is holiday - rule #7 applies.
• Give discount to the selected day including holiday - rule #8 applies.

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 then check to see if it is a holiday.

• Follow by filtering the discount record by prod_id and the period (using start_dt and end_dt).

• 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 it 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.

Jump to #MSSQL blog

Author

Lau Hon Wan, software developer.