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
|
with AR_article (AR_id, AR_lib) as
(select 1, 'canne à pêche' union all
select 2, 'piano droit'
)
, YC_type_client(YC_id, YC_lib) as
(select 1, 'privé' union all
select 2, 'professionnel'
)
, LP_loc_periode(LP_id, LP_lib) as
(select 1, '0 - 5 jours' union all
select 2, '6 - 10 jours'
)
, TA_tarif (YC_id, LP_id, AR_id, TA_prix) as
(select 1, 1, 1, 020 union all
select 1, 2, 1, 025 union all
select 2, 1, 1, 022 union all
select 2, 2, 1, 027 union all
select 1, 1, 2, 120 union all
select 1, 2, 2, 110 union all
select 2, 1, 2, 115 union all
select 2, 2, 2, 135
)
select ART.AR_lib as "Article"
, PER.LP_lib as "Période"
, PRV.TA_prix as "Privé"
, PRO.TA_prix as "Pro"
from AR_article as ART
cross join
LP_loc_periode as PER
left join
(select LP_id
, TA_prix
, AR_id
from TA_tarif as TA1
where TA1.YC_id = 1 -- client privé
) as PRV
on PRV.AR_id = ART.AR_id
and PRV.LP_id = PER.LP_id
left join
(select LP_id
, TA_prix
, AR_id
from TA_tarif as TA2
where TA2.YC_id = 2 -- client pro
) as PRO
on PRO.AR_id = ART.AR_id
and PRO.LP_id=PER.LP_id
order by 1, 2 |
Partager