How to group by based on 2 different values in 2 different subject areas

Hari S

Hi, I need to create a formula from data fetching from below 2 subject areas.

1. Workforce Management - Worker Assignment Real Time
2. Workforce Management - Person Real Time

My Condition is below.

(Employees in G1 & above National + Employees in G2 & Below National)/(All Employees in G1 & above National & Non National + Employee in G2 & Below National)

I have used below SQL code in the analytics to get the data from both subject areas but not able to acheive the expected formula.

SELECT
Citizenship saw_0,
SUM(PersonCount) saw_1,
GradeName saw_2,
SUM(AssignmentCount) saw_3

FROM (

select A.s_1 Citizenship, A.s_2 PersonCount, B.t_1 GradeName, B.t_2 AssignmentCount



from

(


SELECT


   CASE WHEN "Workforce Management - Person Real Time"."Person Citizenship"."Nationality Code" = 'AE' THEN 'Emirati' ELSE 'Expats' END s_1,


   "Workforce Management - Person Real Time"."Person"."Person Count" s_2,

	"Workforce Management - Person Real Time"."Worker"."Person Number" S_3



FROM "Workforce Management - Person Real Time"


) A,


(SELECT


   case when "Workforce Management - Worker Assignment Real Time"."Grade"."Grade Name" in ('G1', 'A', 'B', 'C1', 'C2', 'D1', 'D2', 'E1', 'E2', 'F1', 'F2') THEN 'A'

   when "Workforce Management - Worker Assignment Real Time"."Grade"."Grade Name" in ('G2','G3','H1','H2','H3','I1','I2') THEN 'B'

	ELSE 'NA' END	   t_1,


   "Workforce Management - Worker Assignment Real Time"."Worker Assignment"."Assignment Count" t_2,

"Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" t_3



FROM "Workforce Management - Worker Assignment Real Time"


) B


where A.s_3 = B.t_3

AND B.t_3 NOT LIKE '%PR%'


) final

GROUP BY saw_0,saw_2
ORDER BY saw_0, saw_2

My data looks like below

Could anyone please let me know how to achieve the expected output.

Regards,

Hari

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/23840/how-to-group-by-based-on-2-different-values-in-2-different-subject-areas

Can you share what is the expected output in excel format?

Thanks.