[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Performing Comparisons in Star Schema by Year and Department

Posted on 2014-07-11
3
Medium Priority
?
283 Views
Last Modified: 2014-07-15
Greetings,
I have this Schema
CREATE TABLE `PAY_FACT` (
    `PAYNO` int(11) default NULL,
    `ORGN_CODE` varchar(6) default NULL,
    `POSN` varchar(6) default NULL,
    `EARN_CODE` varchar(3) default NULL,
    `HRS` double default NULL,
    `RATE` double default NULL,
    `AMT` double default NULL,
    `date_id` bigint(20) default NULL,
    KEY `EARN` (`EARN_CODE`),
    KEY `POSN` (`POSN`),
    KEY `ORGN` (`ORGN_CODE`)
)  ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `dept_ref` (
    `CODE` varchar(6) default NULL,
    `TITLE` varchar(35) default NULL,
    KEY `CODE` (`CODE`)
)  ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `earn_ref` (
    `CODE` varchar(3) default NULL,
    `SHORT_DESC` varchar(8) default NULL,
    `LONG_DESC` varchar(30) default NULL,
    KEY `CODE` (`CODE`),
    KEY `SHORT` (`CODE` , `SHORT_DESC`),
    KEY `LONG` (`CODE` , `LONG_DESC`)
)  ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `date_ref` (
  `date_id` bigint(20) NOT NULL,
  `date` date NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  `weekend` char(10) NOT NULL default 'Weekday',
  `day_of_week` char(10) NOT NULL,
  `month` int(11) NOT NULL,
  `month_name` char(10) NOT NULL,
  `month_day` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `FY` int(11) NOT NULL,
  `FQ` int(11) NOT NULL,
  `week_starting_monday` char(2) NOT NULL,
  PRIMARY KEY  (`date_id`),
  UNIQUE KEY `date` (`date`),
  KEY `year_week` (`year`,`week_starting_monday`),
  KEY `month_day` (`month`,`month_day`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Open in new window


I am required to have a SUM of the AMT field in the PAY_FACT table by department and Earning Code.
Department Titles: PAY_FACT.ORGN_CODE = dept_ref.TITLE
Earning Codes: PAY_FACT.EARN_CODE = earn_ref.CODE

This sum is to be between two dates.  In addition, I need five other AMT fields for the Years Prior.
The output I am trying to achieve is this:

DEPT                 EARN_TYPE             CFY_AMT            YR1_AMT   YR2_AMT  YR3_AMT YR4_AMT YR5_AMT
Supply                BiWeekly_PAY            5000                  4500         4200           3800         4500        2600
Admin                Overtime_PAY            1200                 1140           1254           1000           454        1560

Open in new window


My biggest problem is getting the YR1 through YR5.
They are the dates equal back 1 year, 2 years, 3years, 4 years, and 5 years.
NULLS need to be converted to 0's
How do I go about this?
Thanks
0
Comment
Question by:Evan Cutler
3 Comments
 
LVL 5

Expert Comment

by:Bob Bender
ID: 40191149
Start with looking at the ISNULL function.

But, you can use either the ISNULL or COALESCE() function.  

As for your years, maybe using a GROUP BY command.


Personally, define fields with NOT NULL and assign a base value (i.e. 0 in your case).   Saves a lot of hassle.

Bob
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40191638
When using SUM() the concern over nulls is reduced. I think your query will look something like this:
select
        d.TITLE
      , f.EARN_CODE
      , sum(case when dt.`year` = 2010 then AMT else 0 end) yr1
      , sum(case when dt.`year` = 2011 then AMT else 0 end) yr2
      , sum(case when dt.`year` = 2012 then AMT else 0 end) yr3
      , sum(case when dt.`year` = 2013 then AMT else 0 end) yr4
      , sum(case when dt.`year` = 2014 then AMT else 0 end) yr5
from date_ref dt
left join PAY_FACT f on dt.date_id = f.date_id
inner join dept_ref d on f.ORGN_CODE = d.CODE
where dt.`date` >= '2010-01-01' and dt.`date` < '2015-01-01'
group by
        d.TITLE
      , f.EARN_CODE
;

Open in new window

Notice how the date range is selected. It appears using the field `date` will be most effective due to the unique key, and please don't use between for this see: "Beware of Between"
http://sqlfiddle.com/#!9/f45dd/8

----
by the way:

for MySQL use COALESCE()
or
IFNULL()
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

(in SQL Server ISNULL() is the same as IFNULL() for MySQL, darn confusing)
0
 
LVL 9

Author Closing Comment

by:Evan Cutler
ID: 40197133
Thank you so much Paul...
This is very enlightening.
Especially about the between thing.

Evan
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question