follow per day/month in the same query

[master_student]

Hello guys

Hope you're doing very good.

I have a query that contains those fields :

select   to_date('2016-09-26' ,'YYYY-MM-DD') as extract date, inv.billing_period,

sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

sum(total_invoice_amount) as CA

from

.....

where to_date('2016-09-26' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

the billing period is the strat of month 0101 /0102/0103, it is used to follow the CA

The paid amount changes according to the extract date (in a daily way)

The result is something like this :

Extract date	billing_period	paid_amount	CA
2016-09-26	01092016	252	26356

what I need as a result now is to measure the changes of paid amount in a daily way by using the extract date field

what I am doing right now is to concatenate the same query many time to get the sum paid amount acccording to many extract dates :

select   to_date('2016-09-27 ,'YYYY-MM-DD') as extract date, inv.billing_period,

sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

sum(total_invoice_amount) as CA

from

.....

where to_date('2016-09-26' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

concatenate

select   to_date('2016-09-26' ,'YYYY-MM-DD') as extract date, inv.billing_period,

sum(case when pt.segment_type is not null then payment.Total_payment else 0 end) as paid_amount,

sum(total_invoice_amount) as CA

from

.....

where to_date('2016-09-26' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

any other solutions please..

Thanks

[master_student]

Any help please???

[el_aprendiz111]

Hi,

Would you be able to share a sample with the expected output?

in excel

[master_student]

Hi fer fer

Just to clarify my request, bellow are more details :

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