Testing

Before you run the script in Listing 11.2, you need to prepare the following promotion schedule CSV file and save it as promo_schedule.csv.

 PROMOTION CODE,PROMOTION NAME,START DATE, LAST DATE SO,Special Offer,2007-04-01,2007-04-10 DP,Disk Promotion,2007-05-05,2007-05-20 MS,Month Special,2007-06-01,2007-06-30 MS,Monitor Promotion,2007-07-10,2007-07-15 BS,Back to School,2007-08-10,2007-08-30 

Note 

There are 83 promotion dates in the five promotion periods. The first period has 10 dates (April 1, 2005; April 2, 2005;…April 10, 2005), the second 16 dates (May 5, 2005; May 6, 2005;…May 20, 2005), and so on.

You can then run the on_demand.sql script in Listing 11.2 using this command.

 mysql> \. c:\mysql\scripts\on_demand.sql

Here is the response on the MySQL console.

 Database changed Query OK, 1 row affected (0.05 sec) Query OK, 5 rows affected (0.06 sec) Records: 5  Deleted: 0  Skipped: 0  Warnings: 0 Query OK, 83 rows affected (0.08 sec) Rows matched: 83  Changed: 83  Warnings: 0

You can confirm the results (the promotion indicators are set to ‘Y’) by querying the promotion dates in the date_dim table. For example, a query on the first period should give you ten dates with the Y value on their promo_ind columns:

 mysql> select * from date_dim where date >= '2007-04-01' and     -> date <= '2007-04-10' \G

The result is as follows.

 *************************** 1. row ***************************        date_sk: 762           date: 2007-04-01     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 2. row ***************************        date_sk: 763           date: 2007-04-02     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 3. row ***************************        date_sk: 764           date: 2007-04-03     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 4. row ***************************        date_sk: 765           date: 2007-04-04     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 5. row ***************************        date_sk: 766           date: 2007-04-05     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 6. row ***************************        date_sk: 767           date: 2007-04-06     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 7. row ***************************        date_sk: 768           date: 2007-04-07     month_name: April          month: 4        quarter: 2           year: 2007      promo_ind: Y effective_date: 0000-00-00    expiry_date: 9999-12-31 *************************** 8. row ***************************        date sk: 769            date: 2007-04-08     month_name : April           month: 4        quarter : 2           year : 2007       promo_ind: Y effective_date : 0000-00-00     expiry_date: 9999-12-31 *************************** 9. row ***************************         date_sk: 770            date: 2007-04-09     month_name : April          month : 4        quarter : 2           year : 2007       promo_ind: Y effective_date : 0000-00-00     expiry_date: 9999-12-31 *************************** 10. row ***************************         date_sk: 771            date: 2007-04-10     month_name : April           month: 4        quarter : 2           year : 2007       promo_ind: Y effective_date : 0000-00-00     expiry_date: 9999-12-31  10 rows in set (0.01 sec)