if exists(select 1 from sysobjects where name = 'v_item_specprice')
   drop view v_item_specprice 
go

CREATE VIEW v_item_specprice 
as
select 
d.branch_no,
a.item_no,
a.item_subno,
a.item_name,
unit_no = isnull(ut.unit_name, a.unit_no),
a.product_area,
a.item_size,
a.sale_price,
a.vip_price,
spec_sale_price = b.spe_price,
sale_start_date = b.start_date,
sale_end_date = b.end_date,
spec_vip_price = c.spe_price,
vip_start_date = c.start_date,
vip_end_date = c.end_date
  from item a
 inner join ba_stock_info d on 1 = 1
  left join ba_unit_info ut on ut.unit_no = a.unit_no
	left join pm_spec_price b on a.item_no = b.item_no and d.branch_no = b.branch_no and b.special_type = '4' and convert(char(10), getdate(), 120) between b.start_date and b.end_date 
	left join pm_spec_price c on a.item_no = c.item_no and d.branch_no = c.branch_no and c.special_type = '7' and convert(char(10), getdate(), 120) between c.start_date and c.end_date