Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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'
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.
Not sure the SQL equivalent of this, but in QlikView you can use IntervalMatch for handling this kind of situations
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;
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?
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?
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