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 39 40 41 42 43 44 45 46 47
| SELECT dbo_ARTICLES.COD_ART, dbo_ARTICLES.DGN, dbo_ARTICLES.DEB_TRF_1 AS TARIF1, dbo_ARTICLES.PRX_VEN_HT_1 AS PV1, dbo_ARTICLES.DEB_TRF_2 AS TARIF2, dbo_ARTICLES.PRX_VEN_HT_2 AS PV2, Switch(
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")>=[TARIF1] And Format(Now(),"yyyymdd")>[TARIF2], [PV1],
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And [TARIF2]="0",0,
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And Format(Now(),"yyyymmdd")<[TARIF2],0,
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")>=[TARIF1] And Format(Now(),"yyyymmdd")<[TARIF2],[PV1],
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")>[TARIF1] And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]<[TARIF2] And [DEB_TRF_1]="0" And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]<[TARIF2] And [DEB_TRF_1]="0" And Format(Now(),"yyyymmdd")<[TARIF2],0,
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1>PV2,[PV1],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1<PV2,[PV2],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1=PV2,[PV2],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")<[TARIF1],0
) AS TARIFENCOURS, dbo_ARTICLES.STK_REEL, dbo_ARTICLES.QTE_ATE, dbo_ART_DERNIERES_VENTES.Client_Livré, dbo_ART_DERNIERES_VENTES.Date_Dern_Vente, dbo_ART_DERNIERES_VENTES.Qté_Livrée, dbo_ART_DERNIERES_VENTES.Prix_Unitaire_Net, dbo_ART_DERNIERES_VENTES.Ref_Commande, dbo_ART_DERNIERES_VENTES.Numéro_Pièce, dbo_ART_DERNIERES_VENTES.Code_article
FROM dbo_ARTICLES LEFT JOIN dbo_ART_DERNIERES_VENTES ON dbo_ARTICLES.COD_ART = dbo_ART_DERNIERES_VENTES.Code_article
WHERE (((dbo_ART_DERNIERES_VENTES.Client_Livré)="02044"
Or (dbo_ART_DERNIERES_VENTES.Client_Livré) Is Null)
AND ((dbo_ART_DERNIERES_VENTES.Date_Dern_Vente) Is Null
Or (dbo_ART_DERNIERES_VENTES.Date_Dern_Vente)=(SELECT Max([Date_Dern_Vente])
FROM dbo_ART_DERNIERES_VENTES AS x
WHERE x.Code_article = dbo_ARTICLES.[COD_ART] AND x.Client_Livré=dbo_ART_DERNIERES_VENTES.Client_Livré OR x.Client_Livré IS NULL )))
UNION
SELECT dbo_ARTICLES.COD_ART, dbo_ARTICLES.DGN, dbo_ARTICLES.DEB_TRF_1 AS TARIF1, dbo_ARTICLES.PRX_VEN_HT_1 AS PV1, dbo_ARTICLES.DEB_TRF_2 AS TARIF2, dbo_ARTICLES.PRX_VEN_HT_2 AS PV2, Switch(
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")>=[TARIF1] And Format(Now(),"yyyymdd")>[TARIF2], [PV1],
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]>[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And [TARIF2]="0",0,
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")<[TARIF1] And Format(Now(),"yyyymmdd")<[TARIF2],0,
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")>=[TARIF1] And Format(Now(),"yyyymmdd")<[TARIF2],[PV1],
[TARIF1]<[TARIF2] And Format(Now(),"yyyymmdd")>[TARIF1] And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]<[TARIF2] And [DEB_TRF_1]="0" And Format(Now(),"yyyymmdd")>=[TARIF2],[PV2],
[TARIF1]<[TARIF2] And [DEB_TRF_1]="0" And Format(Now(),"yyyymmdd")<[TARIF2],0,
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1>PV2,[PV1],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1<PV2,[PV2],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")>=[TARIF1] And PV1=PV2,[PV2],
[TARIF1]=[TARIF2] AND Format(Now(),"yyyymmdd")<[TARIF1],0
) AS TARIFENCOURS, dbo_ARTICLES.STK_REEL, dbo_ARTICLES.QTE_ATE, dbo_ART_DERNIERES_VENTES.Client_Livré, dbo_ART_DERNIERES_VENTES.Date_Dern_Vente, dbo_ART_DERNIERES_VENTES.Qté_Livrée, dbo_ART_DERNIERES_VENTES.Prix_Unitaire_Net, dbo_ART_DERNIERES_VENTES.Ref_Commande, dbo_ART_DERNIERES_VENTES.Numéro_Pièce, dbo_ART_DERNIERES_VENTES.Code_article
FROM dbo_ARTICLES LEFT JOIN dbo_ART_DERNIERES_VENTES ON dbo_ARTICLES.COD_ART = dbo_ART_DERNIERES_VENTES.Code_article
WHERE (((dbo_ART_DERNIERES_VENTES.Client_Livré)<>"02044"
Or (dbo_ART_DERNIERES_VENTES.Client_Livré) Is Null)
AND ((dbo_ART_DERNIERES_VENTES.Date_Dern_Vente) Is Null
Or (dbo_ART_DERNIERES_VENTES.Date_Dern_Vente)=(SELECT Max([Date_Dern_Vente])
FROM dbo_ART_DERNIERES_VENTES AS x
WHERE x.Code_article = dbo_ARTICLES.[COD_ART] ))); |
Partager