1

I can't seem to find this simple fact anywhere.

I have a fact table like such in SQL server.

fact_picked
Emp_Name     Date     Apples_Picked
John        May 1 17    100

And a type 2 dimension table like this

dim_company
Emp_Name   Company     Effective_Since
John      Blue_Apples      June 1 2015
John      Apple_N_Stuff    Jan 1 2016
John      Da_Big_Apple     March 17 2017
John      Big_Tech         October 20 2017

How would I join the fact to the dimension table so I know which "company" picked the 100 apples?

In this case, logically, given the data, it's 'Da_Big_Apple' ... since John began working there in March 17 until Oct 2017, in which his apple picking task took place.

How do I do a join of these tables though? (assuming thousands of records).

I just get stuck. I know I should do something like

Select fp.emp_name, fp.date, fp.apples_picked
from fact_picked fp
left join dim_company dc
on fp.emp_name = dc.emp_name
and fp.date > dc.effective_since .... ???

I guess I'm not really sure. I guess I can use a view to convert the type 2 to a type 4 (with an end date in the table). Then it's simpler. Make sure the fact date is greater than the start date, yet before the end date. But is that the most elegant solution?

1 Answer 1

3

Setup some sample data:

create table fact_picked
(Emp_Name         varchar(30)
,[Date]           date
,Apples_Picked    int);

insert into fact_picked values
('John','10/17/15',175),
('John','05/01/17',100);

create table dim_company
(Emp_Name         varchar(30)
,Company          varchar(30)
,Effective_Since  date);

insert into dim_company values
('John','Blue_Apples',  '06/01/2015'),
('John','Apple_N_Stuff','01/01/2016'),
('John','Da_Big_Apple', '03/17/2017'),
('John','Big_Tech',     '10/20/2017');

Proposed solution:

select fp.Emp_Name, 
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since    
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | Blue_Apples  | 01/06/2015 00:00:00
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00
  • sub-query is used to find the dim_company record with the latest (ie, max()) Effective_Since date that is less than a given fact_picked.[Date]
  • use the sub-query result to determine which dim_company record to join with in the top-level query

To make sure the left join is working properly, assume we have only the following rows in the dim_company table:

truncate table dim_company;
insert into dim_company values
('John','Da_Big_Apple','03/17/2017'),
('John','Big_Tech',    '10/20/2017');

Run the proposed query again:

select fp.Emp_Name, 
       fp.[Date],
       fp.Apples_Picked,
       dc.Company,
       dc.Effective_Since

from   fact_picked fp

left
join   dim_company dc
on     dc.Emp_Name        = fp.Emp_Name
and    dc.Effective_Since = (select max(Effective_Since)
                             from   dim_company dc2
                             where  dc2.Emp_Name         = fp.Emp_Name
                             and    dc2.Effective_Since <= fp.[Date])

order by 1,2,5;

Emp_Name | Date                | Apples_Picked | Company      | Effective_Since    
-------- | ------------------- | ------------- | ------------ | -------------------
John     | 17/10/2015 00:00:00 |           175 | null         | null               
John     | 01/05/2017 00:00:00 |           100 | Da_Big_Apple | 17/03/2017 00:00:00

And here's a dbfiddle for the above.

3
  • This seems to work for me. It makes logical sense (take the highest date that is before the action date) ... but for some reason, still difficult for me to wrap my head around the subquery (or any query) that references a table not within the subquery (in your case the table fp). I'm not exactly sure the order it runs the query. I thought it would resolve the subquery first, but obviously, that's probably not what it does. Thanks for the help!
    – user45867
    Aug 30, 2017 at 17:01
  • roughly speaking ... a sub-query exists within the scope of the higher-level query(s), which means the sub-query can reference tables from the higher-level portion of the query (eg, dc2 can join with fp); however, the same is not true in the opposite direction, ie, the higher-level query cannot reference tables mentioned in a sub-query (eg, the top-level of this query cannot reference the sub-query's dc2); from a coding perspective we would look at the sub-query as being executed once for each record processed in the top-level query ...
    – markp-fuso
    Aug 30, 2017 at 17:19
  • ,,, in reality, how the query is processed ... the order in which tables are processed ... will depend on the optimizer, availability of stats and indexes, the actual query contents itself, and the run-time query engine; obviously (?) this is a much, much bigger topic (think 'query design and P&T') and will vary between the different RDBMS products
    – markp-fuso
    Aug 30, 2017 at 17:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.