Hi All,
I have the following tables to form a query
create table TEST_HEADER(ID,NUM,OWN,CREATED_DT) as select
1,'1-1','ABC',sysdate-20 from DUAL union all select
1,'1-2','XYZ',sysdate-20 from DUAL union all select
1,'1-3','XXX',sysdate-20 from DUAL union all select
1,'1-4','AAA',sysdate-20 from DUAL union all select
1,'1-5','BBB',sysdate-20 from DUAL union all select
1,'1-6','CCC',sysdate-20 from DUAL
create table TEST_CASETBL(ID,NUM,TYPE,CREATED_BY,CREATED_DT) as select
1,'1-1','CT','IRRELAVANT',sysdate-20 from DUAL union all select
2,'1-1','CT','IRRELAVANT',sysdate-30 from DUAL union all select
3,'1-2','CT','IRRELAVANT',sysdate-30 from DUAL union all select
4,'1-2','UPD','XYZ',sysdate-10 from DUAL union all select
5,'1-3','CT','IRRELAVANT',sysdate-5 from DUAL union all select
6,'1-3','EO','XYZ',sysdate-10 from DUAL union all select
7,'1-4','CT','IRRELAVANT',sysdate-8 from DUAL union all select
8,'1-4','NT','AAA',sysdate-10 from DUAL union all select
9,'1-5','EO','BBB',sysdate-10 from DUAL union all select
10,'1-5','NT','BBB',sysdate-10 from DUAL;
Create table TEST_AUDTBL(ID,NUM,LGN,DT)
1,'1-1','ABC',sysdate-30 from DUAL union all select
2,'1-1','XXX',sysdate-40 from DUAL union all select
3,'1-2','XYZ',sysdate-40 from DUAL union all select
4,'1-3','XYZ',sysdate-20 from DUAL union all select
5,'1-3','111',sysdate-50 from DUAL union all select
6,'1-3','222',sysdate-50 from DUAL union all select
7,'1-3','XXX',sysdate-10 from DUAL union all select
8,'1-4','AAA',sysdate-10 from DUAL ;
TEST_HEADER.OWN=TEST_CASETBL.CREATED_BY
1.I would like to calculate count of NUM,count of different values in 'TYPE' (except for value as 'CT') column based on CREATED_BY Column
2.For TYPE 'CT',Created_by column is populated with IRRELAVANT.So,i need to get the proper value from TEST_AUDTBL by comparing with NUM column
in both the tables(TEST_CASETBL.NUM =TEST_AUDTBL.NUM) and TEST_AUDTBL.DT<=TEST_CASETBL.CREATED_DT and get the first record to get the lgn of TEST_AUDTBL
and need to count the number of CT based on TEST_AUDTBL lgn
I need both the cases to be formed in a single query
Expected result:
CREATED_BY Count(NUM) Count(CT) Count(EO) Count(NT) Count(UPD)
ABC 0 1(which means with id=1 in test_audtbl) 0 0 1
XYZ 2(which means 1-2,1-3) 2(which means with id=3,id=4 in test_audtbl) 1 0 1
XXX 0 0 0 0 0
AAA 1(which means 1-4) 1(which means with id=8 in test_audtbl) 0 1 0
BBB 1(which means 1-5) 0 1 1 0
Thanks,
Chandavi