We are moving

We are moving to our new blog site:

https://ciysys.com/blog/mssql.htm

 

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.

Disabling auto-close in the database

If auto-close has been enabled in the database, it will have negative effects on the hard disk spinning load and also might flood the event log with "starting database..". It has been widely discussed in the Internet why it's not a good idea to enable it.

Anyway, here is the SQL script to disabled auto-close feature.

set nocount on

declare
    @seq int
    , @db sysname
    , @sql nvarchar(max)

select seq = row_number() over (order by name), name
into #db_list
from sys.databases
where is_auto_close_on = 1

set @seq = 0

while exists(
    select *
    from #db_list
    where seq > @seq
)
begin

    select top 1
        @seq = seq
        , @db = name
    from #db_list
    where seq > @seq
    order by seq

    print 'reseting auto close for ' + @db   

    set @sql = 'alter database ' + @db + ' set AUTO_CLOSE off'

    ----print @sql
    exec sp_executesql @sql

end

print 'done'

drop table #db_list

set nocount off

Loop all records

Basically, there is another way of looping which uses CURSOR. But, it requires record locking which may not work as fast as you think.

In this article, we are going to use a temporary table to keep the data and then loop it with "seq" filter. The following example which is printing all table names on the screen. You may modify the PRINT line so that it compose the necessary SQL statement before you print it or execute any dynamic SQL.



set nocount on

declare
    @seq int
    , @name nvarchar(255)

-- declare a temporary table in the memory.
declare @tb table (
    seq int identity(1,1)
    , name nvarchar(255)
)

--copy all the necessary records to the temporary table '@tb'
insert into @tb (name)
select name
from sys.objects
where
    type = 'u'and
    name like 'tb_suggest%'
order by name

-- init the var before use
set @seq = 0


-- start the lopp
while exists(
    select *
    from @tb
    where seq > @seq
)
begin

    -- always load the first record that is greater than
    -- last seq.
    select top 1                --<<=== this is very important!!!
        @seq = seq
        , @name = name
    from @tb
    where seq > @seq
    order by seq                --<<=== this is very important!!!


    -- do whatevery necessary process here.
    -- you may use 'set xxx' to join the sql string before print it out.

    print '@name => ' + @name

end

How to find out which index is missing

To find out the missing index, we have to analyze the information in the following system views:

• sys.dm_db_missing_index_groups
• sys.dm_db_missing_index_group_stats
• sys.dm_db_missing_index_details

You may download the script that combine all the necessary information from the following URL:

   https://gist.github.com/alexsorokoletov/a079629f9e1435c7f81f

And here is the SQL script:

SELECT
    CONVERT (varchar, getdate(), 126) AS runtime,
    mig.index_group_handle, mid.index_handle,
    CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) AS improvement_measure,

    'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
        + ' ON ' + mid.statement
        + ' (' + ISNULL (mid.equality_columns,'')
        + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END + ISNULL (mid.inequality_columns, '')
        + ')'
        + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,

    migs.*, mid.database_id, mid.[object_id]

FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle

WHERE
CONVERT (decimal (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
--and database_id =  DB_ID('my_database')

ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC


How to use it?

• I'm relying on improvement_measure value and I review the top 5 missing index information and then decide if I should create an index. 
• We should not create all the indexes returned by this query. Because some of the missing indexes can be merge into one index.
• We should review the existing indexes and compare against what is missing and then decide the new index. This might involves deleting the existing index before creating a new one.

Life is tough with naming convention especially we want to know how many times that we have reviewed a particular index. My naming rule works this way:

• IX_my_table_1 - this is the first index.
• IX_my_table_2 - this is another index.
• IX_my_table_2_1 - this is the newer version of index where IX_my_table_2 has been dropped and merge with the new missing columns.

Fixing the database state

Sometimes after rebooting the server, the database state might stick in "recovery". Waiting and waiting and rebooting might not change to the normal state.

In this case, we need to fix this issue manually.

To view the current database state:

   SELECT name, state_desc from sys.databases

To fix the problematic database:


   ALTER DATABASE test SET EMERGENCY;
   GO

   ALTER DATABASE test SET SINGLE_USER
   GO

   DBCC CHECKDB (test, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
   GO

   ALTER DATABASE test SET MULTI_USER
   GO

Before running the above commands, please make sure you have done sufficient research on the Internet before executing it!

Running the same stored procedure sequentially

Use the following system stored procedure to block the same stored procedure from running concurrently.

   sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

   sp_releaseapplock

Note:

• The above system stored procedures must be running within a database transaction.
• If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called. 
• If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.