Joining two queries together into one main table

Viewed 36

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



0 Answers
Related