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)