Hi All,
I have the following tables to form a query
create table TEST_HDR(ID,NUM,OWNER) as select
1,'1-1','ABC' from DUAL union all select
2,'1-2','ABC' from DUAL union all select
3,'1-3','AAA' from DUAL union all select
4,'1-4','AAA' from DUAL union all select
5,'1-5','XYZ' from DUAL
create table TEST_ACT(ID,NUM,TYPE,CREATED_BY,CREATED_DT) as select
1,'1-1','CO','ABC',sysdate-10 from DUAL union all select
2,'1-1','EO','ABC',sysdate-30 from DUAL union all select
3,'1-2','CO','AAA',sysdate-20 from DUAL union all select
4,'1-2','CO','ABC',sysdate-30 from DUAL union all select
5,'1-2','EO','ABC',sysdate-25 from DUAL union all select
6,'1-3','CO','ABC',sysdate-35 from DUAL union all select
7,'1-3','CO','AAA',sysdate-35 from DUAL union all select
8,'1-4','CO','AAA',sysdate-12 from DUAL union all select
9,'1-4','CO','AAA',sysdate-22 from DUAL union all select
10,'1-4','EO','AAA',sysdate-22 from DUAL
create table test_audit(ID,NUM,AUDFLD,AUDTVAL,DT,lgn) as select
1,'1-1','Sub-Status','RUP',sysdate-15,'ABC' from DUAL union all select
2,'1-1','Status','Open',sysdate-20,'AAA' from DUAL union all select
3,'1-1','Sub-Status','WIP',sysdate-25,'ABC' from DUAL union all select
4,'1-2','Sub-Status','WIP',sysdate-35,'AAA' from DUAL union all select
5,'1-2','Sub-Status','RUP',sysdate-40,'AAA' from DUAL union all select
6,'1-2','Status','Close',sysdate-20,'ABC' from DUAL union all select
7,'1-3','Status','Open',sysdate-20,'AAA' from DUAL union all select
8,'1-3','Sub-Status','AWI',sysdate-40,'AAA' from DUAL union all select
9,'1-3','Sub-Status','WIP',sysdate-45,'ABC' from DUAL union all select
10,'1-4','Sub-Status','WIP',sysdate-20,'AAA' from DUAL union all select
11,'1-4','Sub-Status','AWI',sysdate-25,'AAA' from DUAL union all select
12,'1-4','Sub-Status','WIP',sysdate-23,'ABC' from DUAL
1.I want to calculate the count of type='CO',Count of distinct Num in TEST_ACT for which type='CO' based on created_by column in test_act
2.Count of NUM where type='CO' based on DT in which 'CO' falls in test_audit table i.e.,TEST_ACT.NUM=TEst_audit.NUM and and TEST_HDR.OWNER=test_audit.lgn and test_Audit.dt<=test_act.created_dt and test_audit.AUDFLD='Sub-Status'
and sort by audit date desc and get the first record to obtain the AUDTVAL to split based on the different values in AUDITVAL
Expected Result:
CREATED_BY COUNT(CO) COUNT(DISTINCT NUM) COUNT(TYPE IS CO IN 'WIP' Sub Status) COUNT(TYPE IS CO IN 'RUP' Sub status) COUNT(TYPE IS CO IN 'AWI' Sub status)
ABC 3(which means 3(which means 1(which means id=4 in test_audit) 1(which means id=1 in test_audit) 1(which means id=8 in test_audit)
id=1,4,6in test_Act table) Num:1-1,1-2,1-3)
AAA 4(which means id=3,7, 3(which means 2(which means id=4,10 in test_audit) 0 2(which means id=8,11 in test_audit)
8,9 in test_act table) Num:1-2,1-3,1-4)
Thanks,
Chandavi