CODEWITHSUNDEEP
plsqlpopulatedimensionstar-schema
StevenC
StevenC

Reputation: 109

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

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.

Upvotes: 0

Views: 652

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

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.

Upvotes: 1

Related Questions