背景:公司在世界各大洲均有业务,机型设置价格,分为国家和区域。国家如有定价取国家价,未定价取国家所属的区域价。做个记录。
alter procedure P_GetSalePrice
@CustID varchar(50), --代理ID
@MaterialID varchar(50) --机型ID
as
declare @CurrentTime datetime
begin
create table #Retable
(
FID int,
FNUMBER varchar(20),
FNAME varchar(20),
F_YEA_BELONGTYPE varchar(20),
FEFFECTIVEDATE datetime ,
FEXPIRYDATE datetime,
F_YEA_AGENT int,
F_YEA_COUNTRY int,
F_YEA_REGION int,
F_YEA_MATERIAL int,
F_YEA_AGENCYPRICE float,
F_YEA_VIPPRICE float,
F_YEA_EXCHANGERATE decimal,
F_YEA_LOCALMAPPRICE float,
F_YEA_LOCALSMRPPRICE float,
F_YEA_MAPPRICE float,
F_YEA_SMRPPRICE float
)
select @CurrentTime=GETDATE() --系统当前时间
insert into #Retable(FID,FNUMBER,FNAME,FEFFECTIVEDATE,FEXPIRYDATE,F_YEA_MATERIAL,
F_YEA_AGENCYPRICE,F_YEA_VIPPRICE,F_YEA_MAPPRICE,F_YEA_SMRPPRICE)
select v.FID,v.FNUMBER,v.FNAME,v.FEFFECTIVEDATE,v.FEXPIRYDATE,v.F_YEA_MATERIAL,
v.F_YEA_AGENCYPRICE,v.F_YEA_VIPPRICE,v.F_YEA_MAPPRICE,v.F_YEA_SMRPPRICE
from V_YEA_SalesPrice v left join T_BD_CUSTOMER c on c.FCUSTID = @CustID
where v.F_YEA_COUNTRY = c.F_YEA_COUNTRY and v.F_YEA_MATERIAL=@MaterialID
and FEFFECTIVEDATE<=@CurrentTime and FEXPIRYDATE>= @CurrentTime
if exists(select * from #Retable) --国家价格不存在,取下一级区域价格
begin
select * from #Retable
end
else
begin
insert into #Retable(FID,FNUMBER,FNAME,FEFFECTIVEDATE,FEXPIRYDATE,F_YEA_MATERIAL,
F_YEA_AGENCYPRICE,F_YEA_VIPPRICE,F_YEA_MAPPRICE,F_YEA_SMRPPRICE)
select v.FID,v.FNUMBER,v.FNAME,v.FEFFECTIVEDATE,v.FEXPIRYDATE,v.F_YEA_MATERIAL,
v.F_YEA_AGENCYPRICE,v.F_YEA_VIPPRICE,v.F_YEA_MAPPRICE,v.F_YEA_SMRPPRICE
from V_YEA_SalesPrice v left join T_BD_CUSTOMER c on c.FCUSTID = @CustID
left join YEA_T_MI_Countries t on c.F_YEA_COUNTRY=t.FID
where v.F_YEA_REGION = c.F_YEA_REGION
and v.F_YEA_MATERIAL=@MaterialID
and FEFFECTIVEDATE<=@CurrentTime and FEXPIRYDATE>= @CurrentTime
select * from #Retable
end
end