|
发表于 2012-7-5 14:32:09
|
显示全部楼层
kzhongw 发表于 2012-7-5 10:21
刚刚帮你做了个跟踪,货位现存量不是单独放在哪一个表里的,而是查询的时候通过临时表来实现的,有别于库存 ...
把跟踪的关键语句发给你吧,我改了下,不然你不能执行。
SELECT max(cs.autoid) as autoid,CS.cWhCode,max(W.cWhName) as cWhName,CS.cPosCode,max(Position.cPosName) as cPosName,CS.cInvCode,CS.cBatch,
isnull(cs.cvmivencode,N'') as cvmivencode,max(v1.cvenabbname) as cvmivenname,max(CS.dMadeDate) AS dMDate, max(CS.iMassDate) AS iMassDate,
max(CS.cMassUnit) as cMassUnit, max(isnull(E.enumname,N'')) AS cMassUnitName, max(CS.dVDate) as dVDate,
sum(CS.iQuantity) as iQuantity, sum(CS.iNum) iNum,max(I.cInvAddCode) as cInvAddCode, max(I.cInvName) as cInvName,
CS.cFree1, CS.cFree2, CS.cFree3, CS.cFree4, CS.cFree5, CS.cFree6,CS.cFree7, CS.cFree8, CS.cFree9, CS.cFree10,
max(v2.enumname) as 有效期推算方式,max(CS.cExpirationdate) as 有效期至,
max(I.cInvStd) as cInvStd,max(I.cInvCCode) AS cInvCCode, max(InventoryClass.cInvCName) AS cInvCName
----------INTO tempdb..TempRepHHCL_UfidaCW06_0
FROM InvPositionSum CS with (nolock)
left JOIN Warehouse W with (nolock) ON W.cWhCode = CS.cWhCode
left JOIN Inventory I ON CS.cinvcode = I.cinvcode
LEFT JOIN Position ON CS.cPosCode=Position.cPosCode LEFT JOIN InventoryClass ON InventoryClass.cInvCCode=I.cInvCCode
LEFT OUTER JOIN v_aa_enum E with (nolock) ON E.EnumCode=convert(nvarchar,CS.cMassUnit) and E.enumType=N'ST.MassUnit'
left join vendor v1 on cs.cvmivencode = v1.cvencode
left join v_aa_enum v2 on v2.enumcode=ISNULL(CS.iExpiratDateCalcu,0) and v2.enumtype=N'SCM.ExpiratDateCalcu'
WHERE (1=1)
AND 1=1 group by CS.cWhCode,CS.cPosCode,CS.cInvCode,CS.cBatch,isnull(cs.cvmivencode,N''),
CS.cFree1, CS.cFree2, CS.cFree3, CS.cFree4, CS.cFree5, CS.cFree6,CS.cFree7, CS.cFree8, CS.cFree9, CS.cFree10
go
|
|