DATE not getting incremented

[master_student]

I would like to get the result my whole query by date of extract, the result would be like this :

and so on for extract date between vDWDate and vToday

Here's my script :

let vDWDate = makedate(2016,9,26);

let vToday = makedate(2016,10,2);

for iDaysBack = 0 to (vToday - vDWDate)

  let vNextDay = Date(vToday - iDaysBack, 'DD/MM/YYYY');

test:

load *

;

select

inv.billing_period,

case when nb_contract_actif > 0 THEn 'a' else 's' end as status 

,ct.customer_type_name ,

coalesce(ca.advisor,'Inconnu') as advisor,

segments.segment_name,

  sum( case when g.cnt >0 then total_invoice_amount/g.cnt else total_invoice_amount end )

as CA,

to_date('$(vDWDate)','DD/MM/YYYY')as datee

from

   ......

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

                                             .....

group  by 1 ,2 ,3,4 ,5, datee;

next

The result is getting incremented as you can see bellow : but the date field is 26/09/2016 (datee) is not getting incremented. it displays 26/06/2016 the minimum date

do you have any idea please??

[sunny_talwar · Accepted solution]

I think you needed to use vNextDay instead of vDWDate in your Select statement because that is the variable which is changing and not vDWDate

LET vNextDay = Date(vToday - iDaysBack, 'DD/MM/YYYY');

test:

load *

;

select

inv.billing_period,

case when nb_contract_actif > 0 THEn 'a' else 's' end as status

,ct.customer_type_name ,

coalesce(ca.advisor,'Inconnu') as advisor,

segments.segment_name,

  sum( case when g.cnt >0 then total_invoice_amount/g.cnt else total_invoice_amount end )

as CA,

to_date('$(vNextDay)','DD/MM/YYYY')as datee

from

  ......

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

                                            .....

group  by 1 ,2 ,3,4 ,5, datee;

[master_student]

Thanks Sunny. You are right , I have one pb is that the date field is not well sorted on the graph:

[sunny_talwar]

Why don't you sort on this?

[master_student]

Yes. It works. Thanks Sunny.