case when then else end
SELECT a.lim_code ,a.lim_bk_code ,d.bk_name ,a.lim_bt_code ,c.bt_name ,b.blood_sum ,b.bag_sum ,a.lim_bu_code ,e.bu_name ,a.lim_validday ,a.lim_value1 ,a.lim_value2 ,a.lim_value3 ,a.lim_value4 ,(case when b.blood_sum<=a.lim_value4 then 4 when b.blood_sum<=a.lim_value3 then 3 when b.blood_sum<=a.lim_value2 then 2 when b.blood_sum<=a.lim_value1 then 1 else 0 end) as lim_flag FROM bd_dict_stock_limit as a left join bd_dict_type as c on a.lim_bt_code=c.bt_code left join bd_dict_kind as d on a.lim_bk_code=d.bk_code left join bd_dict_unit as e on a.lim_bu_code=e.bu_code left join (SELECT bs_bk_code,bs_bt_code,SUM(isnull(bs_num,0)) as blood_sum ,COUNT(bs_sn) as bag_sum from bd_stock where bs_flag>=1 and bs_flag<6 group by bs_bk_code,bs_bt_code) as b on a.lim_bk_code=b.bs_bk_code and a.lim_bt_code=b.bs_bt_code where a.del_flag=0 and (b.blood_sum<=a.lim_value1 or b.blood_sum<=a.lim_value2 or b.blood_sum<=a.lim_value3 or b.blood_sum<=a.lim_value4)