| Filtering data by aggregated columns calculated in a subquery. [message #640928] |
Thu, 06 August 2015 16:11  |
 |
mcrum1
Messages: 1
Registered: August 2015
|
Junior Member |
|
|
Filtering data by aggregated columns calculated in a subquery.
I'm trying to filter my data to show only what I need and I'm struggling.
My goal is to filter the data below to see in a 30 day rolling period, grouped by YearMo and t9.key_ID_9
where:
• Column Amount_30 >= 3500,
• Distinct values in column rcv_cntry_count_3 associated with t9.key_ID_9 >= 3
My sample query is below:
Select
to_char(t1.Date_1, 'YYYYMM') as YearMo,
t1.Cntry_I as SendCountry,
t1.Amt_1 as FaceAmt,
t1.Cntry_E as ReceiveCountry,
t9.key_ID_9
(Select count(distinct(Cntry_E)) as R_Cntry_E_Cnt
From Table_1 t1a
Inner Join Table_6 t2a on t1a.key_ID_1 = t2a.key_ID_2
Where t1a.evnt_tran_evnt_code in ('REC', 'RDT')
And t1a.Cntry_I = 'FR'
And t1a.Cntry_E <> 'FR'
And t10.E_id = t2a.E_id
And (t1a.Date_2 >= t1.Date_2 - (interval '30' day)
And t1a.Date_2 < t1.Date_2)
)rcv_cntry_count_3,
(Select count(*)
From Table_1 t1a
Inner Join Table_6 t2a on t1a.key_ID_1 = t2a.key_ID_2
Where t1a.evnt_tran_evnt_code in ('REC', 'RDT')
And t1a.Cntry_I = 'FR'
And t1a.Cntry_E <> 'FR'
And t10.E_id = t2a.E_id
And (t1a.Date_2 >= t1.Date_2 - (interval '30' day)
And t1a.Date_2 < t1.Date_2)
)Count_30,
(Select Sum(t1a.Amt_1) as amount
From Table_1 t1a
Inner Join Table_6 t2a on t1a.key_ID_1 = t2a.key_ID_2
Where t1a.evnt_tran_evnt_code in ('REC', 'RDT')
And t1a.Cntry_I = 'FR'
And t1a.Cntry_E <> 'FR'
And t10.E_id = t2a.E_id
And (t1a.Date_2 >= t1.Date_2 - (interval '30' day)
And t1a.Date_2 < t1.Date_2)
)Amount_30,
From Table_1 t1
Inner Join Table_2 t5 on t1.key_ID_3 = t5.key_ID_2
Inner Join Table_3 t3b on t5.key_ID_4 = t3b.key_ID_6
Inner Join Table_2 t6 on t1.key_ID_5 = t6.key_ID_2
Inner Join Table_3 t6b on t6.key_ID_4 = t6b.key_ID_6
Inner Join Table_4 t8 on t1.key_ID_7 = t8.key_ID_7
left join Table_5 t4 on t1.key_ID_2 = t4.key_ID_8
Inner Join Table_6 t9 on t1.key_ID_9 = t9.key_ID_2
Inner Join Table_6 t10 on t1.key_ID_1 = t10.key_ID_2
Where (t1.Date_3 >= to_date('06/01/2015', 'mm/dd/yyyy')
And t1.Date_3 < to_date('07/01/2015', 'mm/dd/yyyy'))
And (t1.Date_2 >= to_date('06/01/2015', 'mm/dd/yyyy'))
And t1.evnt_tran_evnt_code in ('REC', 'RDT')
And t1.Cntry_I in ('FR')
[Updated on: Thu, 06 August 2015 17:01] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
