Hi everybody,
I have a query that gives measures for the whole period since 201302 until now in a monthly way. the measures of this query changes according to the date of extract.
form example
date extract
29/09/20016 the CA for the whole period (since 201302 until now 201610) is 120
30/09/20016 the CA for the whole period (since 201302 until now 201610) is 125
For now I am changing the extract date manually like this and join queries :
select
to_date('2016-10-02' ,'YYYY-MM-DD'), // extractdate
inv.billing_period, case when nb_contract_actif > 0 THEn 'a' else 's' end as status ,
ct.customer_type_description,
sum(total_invoice_amount) as CA
from
where to_date('2016-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to
)...
left join (
select
where to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('2016-10-02' ,'YYYY-MM-DD')
left outer join (
where to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('2016-10-02' ,'YYYY-MM-DD')
join
select
to_date('2016-10-01' ,'YYYY-MM-DD'), // extractdate
inv.billing_period, case when nb_contract_actif > 0 THEn 'a' else 's' end as status ,
ct.customer_type_description,
sum(total_invoice_amount) as CA
from
where to_date('2016-10-01' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to
)...
left join (
select
where to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('2016-10-01' ,'YYYY-MM-DD')
left outer join (
where to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('2016-10-01' ,'YYYY-MM-DD')
and so on...
How can I change the extract date in red automatically please for a given period form example from 20160901 until today...
Thanks in advance
