URL: https://linuxfr.org/forums/linux-debutant/posts/changement-de-date-a37adc36-ffa4-4bd6-8cdc-832958c8b126
Title: changement de date
Authors: zanetti
Date: 2019年07月31日T18:20:08+02:00
License: CC By-SA
Tags: 
Score: -4
```sql
<<--select * from sysdirm where system_key ='ASMINBILL' ----Sauvegarder la table bill/call/gprs(creation de table sav)du mois de facturation ---Table call CREATE TABLE EPPIX.call_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.call_hyb20180301; ---Table bill CREATE TABLE EPPIX.bill_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.bill_hyb20180301; ---Table GPRS CREATE TABLE EPPIX.gprs_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.gprs_hyb20180301; ---Table CYCLE CREATE TABLE EPPIX.cycle20180301_bkp20180301 AS SELECT * FROM EPPIX.cycle; --vider les tables bill , call et GPRS TRUNCATE TABLE EPPIX.bill_hyb20180301; TRUNCATE TABLE EPPIX.gprs_hyb20180301; TRUNCATE TABLE EPPIX.call_hyb20180301; ---------------------------- UPDATE eppix.sba_sub_aux sa set sa.sba_inv_account =(select s.sbd_bill_ac_no FROM eppix.sbd_sub_dets s where sa.sba_subscriber_id=s.sbd_subscriber_id and sa.sba_inv_account!= s.sbd_bill_ac_no) where sa.sba_subscriber_id in (SELECT sd.sbd_subscriber_id From eppix.sbd_sub_dets sd INNER JOIN eppix.sba_sub_aux sa ON sa.sba_subscriber_id=sd.sbd_subscriber_id WHERE sa.sba_inv_account!= sbd_bill_ac_no); commit; -------------------------------- update eppix.aux_customers set sr_flag = 'C' where bill_ac_no in ( select distinct sbd_bill_ac_no from eppix.sbd_sub_dets where (sbd_package_code like 'PM%' or sbd_package_code like 'FM%' or sbd_package_code like 'PX%' or sbd_package_code like 'FX%') and sbd_bill_cycle in ('HYB') and sr_flag != 'C'); commit ; Delete from eppix.udv_userdoc_deliv Where udv_subscriber_id is not null And udv_subscriber_id in (select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers Where bill_ac_no=sbd_bill_ac_no And sbd_bill_cycle in('HYB') And sr_flag='C') ; commit ; Insert into eppix.udv_userdoc_deliv(udv_subscriber_id,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears) Select distinct sba_subscriber_id,'INVOICE','PRINT','PDF','BILL',1,'N',0,'N'from eppix.sba_sub_aux Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='INVOICE' and sba_subscriber_id=udv_subscriber_id) And sba_subscriber_id in (select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers Where bill_ac_no=sbd_bill_ac_no And sbd_bill_cycle in('HYB') And sr_flag='S'); Insert into eppix.udv_userdoc_deliv(udv_subscriber_id,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears) Select distinct sba_subscriber_id,'ITEMISED','PRINT','PDF','BILL',1,'N',0,'N'from eppix.sba_sub_aux Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='ITEMISED' and sba_subscriber_id=udv_subscriber_id) And sba_subscriber_id in (select sbd_subscriber_id from eppix.sbd_sub_dets,eppix.aux_customers Where bill_ac_no=sbd_bill_ac_no And sbd_bill_cycle in('HYB') And sr_flag='S'); Insert into eppix.udv_userdoc_deliv(udv_bill_ac_no,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears) Select distinct sba_inv_account,'INVOICE','PRINT','PDF','BILL',1,'N',0,'N' from eppix.sba_sub_aux Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='INVOICE' and sba_inv_account=udv_bill_ac_no) And sba_inv_account in (select distinct sbd_bill_ac_no from eppix.sbd_sub_dets,eppix.aux_customers Where sbd_bill_ac_no=bill_ac_no And sbd_bill_cycle in ('HYB') And sr_flag='C'); Insert into eppix.udv_userdoc_deliv(udv_bill_ac_no,udv_doc_type,udv_delivery,udv_format,udv_address_type,udv_frequency,udv_suspend,udv_period_count,udv_print_arrears) Select distinct sba_inv_account,'ITEMISED','PRINT','PDF','BILL',1,'N',0,'N' from eppix.sba_sub_aux Where not exists(select * from eppix.udv_userdoc_deliv where udv_doc_type='ITEMISED' and sba_inv_account=udv_bill_ac_no) And sba_inv_account in (select distinct sbd_bill_ac_no from eppix.sbd_sub_dets,aux_customers Where sbd_bill_ac_no=bill_ac_no And sbd_bill_cycle in('HYB') 5 And sr_flag='C'); commit; --------------------------------------- Mise à jour date ----- UPDATE eppix.VSM_SERVICE_MSISDN SET vsm_billed_upto = TO_DATE('2018-02-28','YYYY-MM-DD')-- Update here last day of month # ça doit mettre le dernier jour du mois <<dernier jour du mois M-1>> <<la facturation se fait à M+1>>
WHERE vsm_subscriber_id IN(
 Select distinct sbd_subscriber_id from eppix.sbd_sub_dets ,vas_active_service vas
 Where vas_subscriber_id =sbd_subscriber_id
 and sbd_bill_cycle='HYB'
 and vas_sub_charge1 is not null
 and vas_sub_charge1 != ' '); 
commit;
----------------------------------------Mise à jour service SPD
update vas_active_service set vas_sub_charge1=vas_service_code where vas_service_code like 'SPD%' and vas_service_code!=vas_sub_charge1 
commit;>>
```
Bonsoir à tous et à toutes je viens par ce message solliciter votre aide.
Je voudrais écrit un script qui me permet de créer deux variables date :
1) une première variable date dont la valeur va me permettre de changer ma date à ce niveau.
```sql
<<---table call CREATE TABLE EPPIX.call_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.call_hyb20180301; ---Table bill CREATE TABLE EPPIX.bill_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.bill_hyb20180301; ---Table GPRS CREATE TABLE EPPIX.gprs_hyb20180301_bkp20180301 AS SELECT * FROM EPPIX.gprs_hyb20180301; ---Table CYCLE CREATE TABLE EPPIX.cycle20180301_bkp20180301 AS SELECT * FROM EPPIX.cycle; --vider les tables bill , call et GPRS TRUNCATE TABLE EPPIX.bill_hyb20180301; TRUNCATE TABLE EPPIX.gprs_hyb20180301; TRUNCATE TABLE EPPIX.call_hyb20180301;>>
```
c'est à dire la variable date change seulement l'année et le mois en chaque année en cours et chaque mois en cours mais ne touche pas le jour qui est 01 lui reste intacte.
2) La seconde variable date dont la valeur va me permettre de changer ma date à ce niveau
```sql
<<update eppix.VSM_SERVICE_MSISDN SET vsm_billed_upto = TO_DATE('2018-02-28','YYYY-MM-DD')-- Update here last day of month # ça doit mettre le dernier jour du mois <<dernier jour du mois M-1>> <<la facturation se fait à M+1>>
WHERE vsm_subscriber_id IN(
 Select distinct sbd_subscriber_id from eppix.sbd_sub_dets ,vas_active_service vas
 Where vas_subscriber_id =sbd_subscriber_id
 and sbd_bill_cycle='HYB'
 and vas_sub_charge1 is not null
 and vas_sub_charge1 != ' '); 
commit;>> 
```
C'est à dire que la valeur de ma deuxième variable date me permettra de changer la date de mon fichier chaque fin de mois.
le tout sera réorienté dans un fichier.sql
bonne nuit à vous et merci d'avancean
</div><div class="naked_ctrl">
<form action="/index.cgi/contrast" method="get" name="gate">
<p><a href="http://altstyle.alfasado.net">AltStyle</a> によって変換されたページ <a href="httpS://linuxfr.org/forums/linux-debutant/posts/changement-de-date-a37adc36-ffa4-4bd6-8cdc-832958c8b126.md">(-&gt;オリジナル)</a>
/ <label>アドレス: <input type="text" name="naked_post_url" value="httpS://linuxfr.org/forums/linux-debutant/posts/changement-de-date-a37adc36-ffa4-4bd6-8cdc-832958c8b126.md" size="22" /></label> <label>モード: <select name="naked_post_mode">
<option value="default">デフォルト</option>
<option value="speech">音声ブラウザ</option>
<option value="ruby">ルビ付き</option>
<option value="contrast" selected="selected">配色反転</option>
<option value="larger-text">文字拡大</option>
<option value="mobile">モバイル</option>
</select>
<input type="submit" value="表示" />
</p>
</form>
</div>