日期:2014-05-16 浏览次数:20681 次
select a.MaterialNO,a.ActualNum ,
case when isnull(b.ismonthsettle,0)=0 then isnull(c.AccountStock,0) else isnull(d.AccountStock,0) end 庫存量,
case when a.ActualNum>case when isnull(b.ismonthsettle,0)=0 then isnull(c.AccountStock,0) else isnull(d.AccountStock,0) end
then 'true' else 'false' end as 判斷結果
from (Select MaterialNO,sum(ActualNum) ActualNum from #tmpWarehouse_Send_list group by MaterialNO) a
inner join cyerp..tblbase_warehouse_materialBill b
on a.materialno = b.materialno and b.sh_status=1 and b.ishide=0 and b.ishide=0
left join cyerp..tblWarehouse_Stock_Materiel c on a.materialno = c.materialno
left join cyerp..tblWarehouse_Stock_MonthMaterial d on a.materialno = d.materialno
where a.ActualNum>case when isnull(b.ismonthsettle,0)=0 then isnull(c.AccountStock,0) else isnull(d.AccountStock,0) end
/*
materialno actualnum 庫存量 判斷結果
K01885 700.000 9050.00000000 false
K01902 10.000 154.00000000 false
K01903 30.000 162.00000000 false
K01905 20.000 125.00000000 false
K01912 6.000 46.00000000 false
K01914 5.000 39.00000000 false
K01922 80.000 7000.00000000 false
K01933 10.000 26.00000000 false
K93412 1.000 20.00000000 false
K93420 1.000 213.00000000 false
K93421 1.000 6.00000000 false
K93484 5.000 433.00000000 false
K93751 100.000 4454.00000000 false
K93752 5.000 815.00000000 false
K93829 4.000 24.00000000 false
K93902 20.000 30.00000000 false
N32011055 2.000 8.00000000 false
N800103 120.000 19746.00000000 false
N800104 120.000 852.00000000 false
N800105 500.000 10450.00000000 false
N800801 3.000 15.00000000 false
/*