1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
|
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as mni
SQL>
SQL> With Commande As (
2 Select '1005500001' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A92007' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('12/04/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
3 Select '1005500001' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A92008' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('13/05/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
4 Select '1005500001' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A92009' CODE_PRODUIT, 'ENCOURS' STATUS, To_Date('24/06/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
5 Select '1005500001' NUMERO_CLIENT, 'MIXEUR01' REFERENCE, '15800A92007' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('12/04/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
6 Select '1005500001' NUMERO_CLIENT, 'MIXEUR01' REFERENCE, '15800A92008' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('14/05/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
7 Select '1005500001' NUMERO_CLIENT, 'MIXEUR01' REFERENCE, '15800A92009' CODE_PRODUIT, 'ENCOURS' STATUS, To_Date('24/06/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
8 Select '1005500002' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A90025' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('12/04/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
9 Select '1005500002' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A97777' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('15/05/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
10 Select '1005500002' NUMERO_CLIENT, 'BROYEUR01' REFERENCE, '15800A98174' CODE_PRODUIT, 'ENCOURS' STATUS, To_Date('24/06/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
11 Select '1005500002' NUMERO_CLIENT, 'MIXEUR04' REFERENCE, '15800A90025' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('12/04/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
12 Select '1005500002' NUMERO_CLIENT, 'MIXEUR04' REFERENCE, '15800A97777' CODE_PRODUIT, 'DELIVRE' STATUS, To_Date('16/05/2013','DD/MM/YYYY') DATE_COMMANDE From dual union all
13 Select '1005500002' NUMERO_CLIENT, 'MIXEUR04' REFERENCE, '15800A98174' CODE_PRODUIT, 'ENCOURS' STATUS, To_Date('24/06/2013','DD/MM/YYYY') DATE_COMMANDE From dual
14 )
15 Select NUMERO_CLIENT,
16 REFERENCE,
17 Min(CODE_PRODUIT) Keep (Dense_Rank First Order by DATE_COMMANDE) As CODE_PRODUIT,
18 Min(STATUS) Keep (Dense_Rank First Order by DATE_COMMANDE) As STATUS,
19 Min(DATE_COMMANDE)
20 From Commande
21 Where date_commande between To_Date('20/04/2013','DD/MM/YYYY') And to_date('30/06/2013','DD/MM/YYYY')
22 Group By NUMERO_CLIENT, REFERENCE
23 Order By NUMERO_CLIENT, REFERENCE
24 /
NUMERO_CLIENT REFERENCE CODE_PRODUIT STATUS MIN(DATE_COMMANDE)
------------- --------- ------------ ------- ------------------
1005500001 BROYEUR01 15800A92008 DELIVRE 13/05/2013
1005500001 MIXEUR01 15800A92008 DELIVRE 14/05/2013
1005500002 BROYEUR01 15800A97777 DELIVRE 15/05/2013
1005500002 MIXEUR04 15800A97777 DELIVRE 16/05/2013
SQL> |
Partager