Populate Date Dimension Table and Determine Day Type via PL/SQL

Question

I have the following SQL Table:

CREATE TABLE SALES
(saleID         INT     PRIMARY KEY,
grossSalePrice      DECIMAL(9,2),
vehicleStatus       VARCHAR(10) NOT NULL CHECK (lower(vehicleStatus) IN ('available', 'sold', 'pending')),
saleDate        DATE,
saleMileage     INT,
customerID      INT,
salespersonID       INT,
vehicleVIN      VARCHAR(25),
CONSTRAINT SALES_FK1 FOREIGN KEY (customerID) REFERENCES CUSTOMERS(customerID),
CONSTRAINT SALES_FK2 FOREIGN KEY (vehicleVIN) REFERENCES VEHICLES(vehicleVIN),
CONSTRAINT SALES_FK3 FOREIGN KEY (salespersonID) REFERENCES SALESPERSONS(salespersonID));

AND I've created the following Star-Schema Dimension Table:

CREATE TABLE TIMES
(saleDay        DATE        PRIMARY KEY,
dayType         VARCHAR(50) NOT NULL);

I need to populate the saleDay column of the TIMES table with each saleDate within the SALES table. Also, the dayType column should be completed based on each saleDay. The values for the dayType column should be Weekday, Weekend, or Holiday and if the dayType is Holiday it should trump Weekend or Weekday.

To qualify for being a Holiday, the dates would have to be: January 1, January 15, January 19, May 28, July 4, October 8, November 11, November 22, December 25.

I've been asked to populate the saleDay and dayType values via PL/SQL but I have NO Idea where to start or how to accomplish this. Any help/guidance as to how I can complete this is greatly appreciated.

score 1 · Accepted Answer · answered Feb 20 '18 at 08:07

There is no need to use PL/SQL.

For holidays use the following:

select '01.01' hd from dual union all
select '15.01' from dual union all
select '19.01' from dual union all
select '28.05' from dual union all
select '04.07' from dual union all
select '08.10' from dual union all
select '11.11' from dual union all
select '22.11' from dual union all
select '25.12' from dual

To calculate weekend: to_char(saleDate, 'd'). It returns a number of day in the week. But be careful here, it depends on NLS settings. If NLS_TERRITORY in settings is AMERICAN, Sunday is the first day of week, in some other territories (European countries, for example) it is 7th day.

So the query is:

insert into times (saleDay, dayType)
select saleDate, case when h.hd is not null then 'Holiday'
                      when to_char(saleDate, 'd') in (1,7) then 'Weekend' -- I suppose you are American
                      else 'Weekday' end dayType
   from sales s left join 
       (<holiday query above>) h
        on h.hd = to_char(s.saleDate, 'dd.mm')

If you need to use it in PL/SQL, you can execute the query inside a procedure.

Ugh - such a simple thing! I just have a hard time taking an idea on paper and putting it into effect. Great example, thank you so much! — StevenC, Feb 20 '18 at 12:39
Any specific way that this should be implemented as PL/SQL? I've tried a create or replace procedure here: http://sqlfiddle.com/#!4/a0e5e/13 and although it executes without error, it doesn't seem to be populating my table. — StevenC, Feb 20 '18 at 13:16
@StevenC As I understood, you executed script, which creates a procedure. But after that you have to execute the procedure itself, like `begin insertTIMES; commit; end; /`. I executed this and the table is populated now: http://sqlfiddle.com/#!4/8ebc8/14 So your procedure is correct. — Dmitriy, Feb 20 '18 at 13:36

Populate Date Dimension Table and Determine Day Type via PL/SQL

1 Answers1