I need to populate my fact table with data from lds_placement table. I have selected the records and here is what it looks like:
fk1_account_id | fk3_job_role_id | salary | no_of_placements | YEAR
---------------------------------------------------------------------
10 | 3 | 165000 | 5 | 2010
10 | 3 | 132000 | 4 | 2011
10 | 3 | 132000 | 4 | 2012
20 | 2 | 990000 | 3 | 2010
20 | 2 | 132000 | 2 | 2011
20 | 2 | 132000 | 2 | 2012
I want to insert time_id from a different table called time_dim into the column year and not the actual year itself.
The time_dim table looks like this:
time_id | year
---------------
5 | 2015
1 | 2013
2 | 2010
3 | 2014
4 | 2012
6 | 2011
I need to insert into "year" column is actually:
year
2
6
4
2
6
4
Please give me the way to insert time_id instead of year in the table.
Here is the code I used to select the top-most table.
SELECT
fk1_account_id,
fk3_job_role_id,
Sum(actual_salary) AS salary,
Count(1) AS no_of_placements,
MAX(EXTRACT(YEAR FROM plt_estimated_end_date)) AS year
FROM lds_placement
GROUP BY fk1_account_id, fk3_job_role_id, EXTRACT(YEAR FROM plt_estimated_end_date)
ORDER BY fk1_account_id;
Use a left join if you want to capture records where year doesn't exist in time_dim. Else use inner_join.
select t.fk1_account_id,t.fk3_job_role_id,t.salary,t.no_of_placements
,d.time_id
from
(SELECT fk1_account_id, fk3_job_role_id, Sum(actual_salary) as salary, Count(1) as no_of_placements, MAX(EXTRACT(YEAR FROM plt_estimated_end_date)) AS YEAR
FROM lds_placement
GROUP BY fk1_account_id, fk3_job_role_id, EXTRACT(YEAR FROM plt_estimated_end_date)
)t
left join time_dim d
on t.year=d.year
order by t.fk1_account_id