CHANGE date dynamically

[master_student]

Hello guys,

I really need your help..

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'

I need to execute this query so many times  to get a snapshot by changing the extract date

This is the desirable result :

for now I am doing like this in my script :

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-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

nocancatenate

SELECT

to_date('2016-09-30' ,'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-30' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

nocancatenate

SELECT

to_date('2016-10-02' ,'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-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

How to do it dynamically ??  Any idea please?? do  have to create a loop??

Thanks

[adamdavi3s]

Ok....

There must be a better way of structuring this.

Load all of your data, then calculate the to_date based on 'x'

I assume that your to_Date is always a sunday, so maybe create a calendar table and use that?

Sorry just going to a meeting or I would knock up an example.

[master_student]

I need to do a loop like that :

date min=26092016

date max =01102016

for i between date min and date max

execute the query; the parametre i should change inside the query

SELECT

to_date('2016-10-02' ,'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-10-02' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

and billing_period >='200305'

[jonathandienst]

That will still be quite inefficient as you are doing a round trip to the server for each record. This will be OK for a few records, but will be painfully slow even for a few hundreds of rows.

I suggest that you look carefully at what  you are trying to achieve and create a (single) query that will fetch the payment amounts grouped by some sort of date. Although it looks like your problem is that the date field you require does not exist in the database. you may need to move some of the logic of selecting the dates to the SQL query.

[sunny_talwar]

Not sure the SQL equivalent of this, but in QlikView you can use IntervalMatch for handling this kind of situations

[master_student]

I am doing this : but the result of the query is only one date 29-09-2016, I didn't get the other dates...

is there any pb with my script??

Let vDWDate ='2016-09-26';

LET vToday ='2016-10-02';

Do until vDWDate=vToday

Temp:

LOAD *;

;

select 

to_date('$(vDWDate)' ,'YYYY-MM-DD') ,

inv.billing_period,

...

from

..

  where to_date('$(vDWDate)' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

  )BSeg

  and to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('$(vDWDate)' ,'YYYY-MM-DD')

if vDWDate=vToday then

Exit Do;

[master_student]

Hello Sunny,

I am doing this : but the result of the query is only one date 29-09-2016, I didn't get the other dates...

is there any pb with my script??

Let vDWDate ='2016-09-26';

LET vToday ='2016-10-02';

Do until vDWDate=vToday

Temp:

LOAD *;

;

select

to_date('$(vDWDate)' ,'YYYY-MM-DD') ,

inv.billing_period,

...

from

..

  where to_date('$(vDWDate)' ,'YYYY-MM-DD') between cas.valid_from and cas.valid_to

  )BSeg

  and to_date(ip.Payment_Received_Date,'yyyy-mm-dd') <=to_date('$(vDWDate)' ,'YYYY-MM-DD')

if vDWDate=vToday then

Exit Do;

any idea please?

[adamdavi3s]

I can't see where you're increment your date in this?

You could also just use today() rather than loading it in a variable (I think)

You also need to dollar sign expand your comparison I think $(DWDate)= today()

However I still don't think you need to do this in your script, I think you can pull all the data and then use Qlik functionality to work out your intervals. Could you provide a sample of the data?

[master_student]

Hi Adam,

Thanks for your reply.

My query returns the measures from the whole period (since 200301 until today)

I use the extract date to get a screenshot for all that period, for example extract date = today, the amount are different from the extract date 2016-09-26

What I am doing now is to join the queries and change the extract date

I would like to do it dynamically