救人第二命

lodge

最初由 xzh2000 发布
[B]

如果DATE1或DATE2是不確定的值?
總之視圖是不行的。 [/B]

哦, 原来是视图的问题, 没看清楚,

sky721

CASE语句已经做出，但取相同的item code的最低purchase_price的语句不会写了
SELECT department,item_code,supplier_code,store_code,'purchase_price' =
CASE
WHEN getdate()>(begin_year+'/'+begin_month+'/'+begin_day) and getdate()<(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_p
WHEN getdate()<(begin_year+'/'+begin_month+'/'+begin_day) or getdate() >(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_n
END
FROM fresh_purchase_price

sky721

终于写完了，谢谢大家
select * from (Select department, item_code, min(purchase_price)as purchase_price from (SELECT *,'purchase_price' =
CASE
WHEN getdate()>(begin_year+'/'+begin_month+'/'+begin_day) and getdate()<(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_p
WHEN getdate()<(begin_year+'/'+begin_month+'/'+begin_day) or getdate() >(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_n
END
FROM fresh_purchase_price) as a
Group by department, item_code) as b inner join (SELECT *,'purchase_price' =
CASE
WHEN getdate()>(begin_year+'/'+begin_month+'/'+begin_day) and getdate()<(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_p
WHEN getdate()<(begin_year+'/'+begin_month+'/'+begin_day) or getdate() >(end_year+'/'+end_month+'/'+end_day) THEN purchase_price_n
END
FROM fresh_purchase_price) as c on b.department=c.department and b.item_code = c.item_code
and b.purchase_price=c.purchase_price

ccwlm741212

多实践就可以了

sky721

最初由 ccwlm741212 发布
[B]多实践就可以了 [/B]

以后还要多指点，虽然写出来了，还是有点弧度