I have the following two queries that I need to join together to get one final output.
The primary keys are year, month,cost_center, g.account, and e.submapping.
The first table is an acutals table from a GL while the second query is a planned amount. I am trying to compare actuals to plan and need to join these two tables with the primary keys listed.
---Query 1 (e)
with Ranking as (
select c.*, rank() over(partition by
c.ledger_name,
c.company_code,
c.location_code,
c.cost_center,
c.account_number,
c.period_name,
c.currency,
c.amount,
c.entered_amount,
c.dm_insert_dt
order by dw_last_updated DESC) rnk
from wbr_global.gl_ap_details c
where cost_center IN ('1168','1153','1172','1257')
and ledger_name IN ('ABC.com, Inc.')
and account_number between '59999' and '70000')
select
to_date(period_name, 'Mon-YY') as BalanceDate,
date_part(month,(to_date(period_name, 'Mon-YY'))) as Months,
Case
when right(period_name,2) = right(date_part_year(current_date),2) then 'Current Year'
when right(period_name,2) = right(date_part_year(current_date) - 1,2) then 'Prior Year'
else 'Error' end as YearFlag,
EXTRACT(YEAR from balancedate) as year,
EXTRACT(Month from balancedate) as month,
e.sub_mapping,
c.cost_center,
c.period_name,
sum(c.amount) as amount
from Ranking c
left join wbr_global.raw_station_extended_attribute d on c.location_code = d.location_code
left join sandbox.utr_fixed_mapping_na e on c.account_number = e.account_number
where c.cost_center IN ('1168','1153','1172','1257')
and c.ledger_name = 'abc.com, Inc.'
and c.rnk = 1
group by 1,2,3,4,5,6,7,8
limit 10
Here is the second query ---Second query g
EXTRACT(YEAR from first_day_of_month) as year,
EXTRACT(Month from first_day_of_month) as month,
first_day_of_month,country,cost_center,scenario,'employee_relation' as account,
sum(employee_relation) as amount
from prophecy_to_sabre_ds_db.prophecy_caffeine.output_ww_field_opex
where cost_center = 1172
AND scenario = 'Q2G_22_1'
group by 1,2,3,4,5,6
Would I be able to make one a sub query in the other and join that way.
(Query 2) as g on g.cost_center = c.cost_center
and g.account = e.sub_mapping
and g.year = e.year
and g.month = e.month