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 48 49 50 51 52 53 54 55 56 57 58 59 60 61
| select distinct(ac.AC_categorie),ct.CT_INTITULE,
PRIXVEN='Gamme',
COEF=
case
when ac.ac_coef!=0 then convert(varchar(15),ac.ac_coef)
when ac.ac_devise != 0 then ' '
else
convert(varchar(15),a.ar_coef)
end,
ac_prixttc,
REMISE=
case
when ac.ac_qtemont =0 then convert(varchar(15),ac.AC_Remise)
else
'Gamme'
end,
ac.ac_arrondi,ac.ac_qtemont
from f_artclient ac
left join p_cattarif ct on ct.cbindice=ac.AC_categorie
left outer join F_ARTICLE a on a.ar_ref=ac.ar_ref
left outer join F_TARIFGAM tg on tg.ar_ref=ac.ar_ref
WHERE ac.ar_ref = 'BAAR01' and AC_categorie > 0 and ISNUMERIC(substring(tg.TG_RefCF,2,2))=1 and substring(tg.TG_RefCF,2,2)=ac.AC_categorie
UNION
select distinct(ac.AC_categorie),ct.CT_INTITULE,
PRIXVEN=
case
when ac.ac_prixven != 0 then convert(varchar(15),ac.ac_prixven)
when ac.ac_coef!=0 then convert(varchar(15),a.ar_prixach * ac.ac_coef)
when ac.ac_devise != 0 then ' '
else
convert(varchar(15),a.ar_prixven)
end,
COEF=
case
when ac.ac_coef!=0 then convert(varchar(15),ac.ac_coef)
when ac.ac_devise != 0 then ' '
else
convert(varchar(15),a.ar_coef)
end,
ac_prixttc,
REMISE=
case
when ac.ac_qtemont =0 then convert(varchar(15),ac.AC_Remise)
else
'Gamme'
end,
ac.ac_arrondi,ac.ac_qtemont
from f_artclient ac
left join p_cattarif ct on ct.cbindice=ac.AC_categorie
left outer join F_ARTICLE a on a.ar_ref=ac.ar_ref
WHERE ac.ar_ref = 'BAAR01' and AC_categorie > 0
and ac.AC_categorie not in (select substring(tg2.TG_RefCF,2,2)from f_tarifgam tg2
where ISNUMERIC(substring(tg2.TG_RefCF,2,2))=1 and tg2.ar_ref = ac.ar_ref)
order by AC_categorie |
Partager