Welcome to ciysys blog

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

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:

Rule # Is holiday Day of week Exclude holiday Include holiday Give a discount?
1 [ ] [x] [ ] [ ] [x]
2 [ ] [ ] [ ] [ ] [ ]
3 [x] [x] [ ] [ ] [x]
4 [x] [x] [x] [ ] [ ]
5 [x] [x] [ ] [x] [x]
6 [x] [ ] [ ] [ ] [ ]
7 [x] [ ] [x] [ ] [ ]
8 [x] [ ] [ ] [x] [x]

The decision table explanations

Use case

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)
                )
        ))
    )

Jump to #MSSQL blog

Author

Lau Hon Wan - the developer and business owner.