金蝶K3即时库存数据提取语句

select
t1.fnumber as F1I31128l9507457,–物料编码
t1.fname Ff131K1281030445,–物料名称
t.fbatchno as Fe1R31128949515,–批号
t3.fname as F18l3112895P126,–仓库
t4.fname as FTRP4NM,–仓位
t.fqty as F13g18212895J144,–库存
case when t.FKFDate=’’ then null else CONVERT(datetime,t.FKFDate,120) end FIH7GS6,–生产采购日期
case when t.FKFPeriod=0 then null else t.FKFPeriod end FF8QOSP,–保质期
case when t.FKFDate=’’ then null else dateadd(d,t.FKFPeriod,t.FKFDate) end FFBPPF8,–到期日
t6.FName FBAHI4F–基本计量单位
FROM AIS20141103101148.[dbo].icinventory t
INNER JOIN AIS20141103101148.[dbo].t_ICItem t1 ON t1.FItemID = t.FItemID
INNER JOIN AIS20141103101148.[dbo].t_Stock t3 ON t3.FItemID = t.fstockid
INNER JOIN AIS20141103101148.[dbo].t_StockPlace t4 ON t4.FSPID = t.FStockPlaceID
inner join AIS20141103101148.[dbo].t_ICItem t5 on t5.FItemID=t.FItemID
inner join AIS20141103101148.[dbo].t_MeasureUnit t6 on t6.FItemID=t5.FUnitID
WHERE t.fqty > 0
提取效果如下：