-1

i want to store data per week per file.

Actually we are on week 43.

i want to have data for :

week 42 stored in abc_42.qvd

week 41 stored in abc_41.qvd

week 40 stored in abc_40.qvd

week 39 stored in abc_39.qvd

here is the query used :

SELECT e.cli_id

, seg_rfm, seg_semestres

, 20182 semestre

, decode(reseau,'W','Web','Mag') Canal

, adr_pays pays

, Sum(clients.conv.get_montant(dev_iso_tenue,'EUR',dat_tic,'$$',ca_ttc_dev)) CA_TTC_EUR

, Sum(nb_art) NB_ART

, Sum(Decode(Sign(ca_ttc_dev),1,1,0)) NB_CDE, sysdate dat_maj

FROM clients.tic_entete e

join clients.cli_fiche f on f.cli_id=e.cli_id

join mag_magasins m on m.magasin = e.magasin

join seg_rfm s on s.cli_id = f.cli_id and s.semestre = 20181

WHERE dat_vte between clients.seg.prem_jour(20182) AND promod.ansem_date(201831,7)

GROUP BY e.cli_id

, seg_rfm, seg_semestres

, decode(reseau,'W','Web','Mag')

, adr_pays

;

thanks

HamzaNig
  • 1,019
  • 1
  • 10
  • 33
kamerwar Wrboy
  • 1
  • 2

1 Answers1

0

First of all, if you want to store data per week, you need to group by week your data.I don't know if you did this, but I suppose you did it.

Then you need to loop throw every week. I make an example:

Storing data per week for the last 6 weeks.

LET vWeekStart = Week(Today)-6;
LET vWeekEnd = Week(Today);

FOR i = $(vWeekStart) to $(vWeekEnd)

  DATA:
  LOAD
      Week,
      20182 as Semestre,
      Sum(clients.conv.get_montant(dev_iso_tenue,'EUR',dat_tic,'$$',ca_ttc_dev)) CA_TTC_EUR,
      Sum(nb_art) NB_ART,
      Sum(Decode(Sign(ca_ttc_dev),1,1,0)) NB_CDE,
      Max(sysdate) as dat_maj
  FROM 'Table'
  WHERE Week(dat_vte) = $(i)
  GROUP BY Week
  ;

Next $(i)

You need to aggregate your data to a week level and loop throw this set using the week number of the date in the "where" clause.

In the example I create 2 variables to do the loop between the actual week and 6 week ago.

dreTa
  • 27
  • 1
  • 3