Performing Comparisons in Star Schema by Year and Department

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
LVL 9
Evan CutlerVolunteer Chief Information OfficerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob BenderCommented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Evan CutlerVolunteer Chief Information OfficerAuthor Commented:
Thank you so much Paul...
This is very enlightening.
Especially about the between thing.

Evan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.