查询U871库存数据

SELECT * FROM

(

SELECT [cWhCode],max([cWhName]) as [cWhName],[cInvCode],max([cInvAddCode]) as [cInvAddCode],max([cInvName]) as [cInvName],max([cInvDefine7]) as [cInvDefine7],[cInvStd],max([cInvCCode]) as
[cInvCCode],[cFree2],max([cInvCName]) as [cInvCName],sum([iQtty]) as [iQtty],max([cInvM_Unit]) as [cInvM_Unit],max([cInvA_Unit]) as [cInvA_Unit],sum([iExchRate]) as
[iExchRate],max([cInvDefine1]) as [cInvDefine1],max([cInvDefine2]) as [cInvDefine2],max([cInvDefine3]) as [cInvDefine3],max([cInvDefine4]) as [cInvDefine4],max([cInvDefine5]) as
[cInvDefine5],max([cInvDefine6]) as [cInvDefine6],max([cInvDefine8]) as [cInvDefine8],max([cInvDefine9]) as [cInvDefine9],max([cInvDefine10]) as [cInvDefine10],sum([cInvDefine11]) as
[cInvDefine11],sum([cInvDefine12]) as [cInvDefine12],sum([cInvDefine13]) as [cInvDefine13],sum([cInvDefine14]) as [cInvDefine14],max([cInvDefine15]) as [cInvDefine15],max([cInvDefine16]) as
[cInvDefine16],max([cFree1]) as [cFree1],max([cFree3]) as [cFree3],max([cFree4]) as [cFree4],max([cFree5]) as [cFree5],max([cFree6]) as [cFree6],max([cFree7]) as [cFree7],max([cFree8]) as
[cFree8],max([cFree9]) as [cFree9],max([cFree10]) as [cFree10],max([cBatch]) as [cBatch],max([cvmivencode]) as [cvmivencode],sum([iNum]) as [iNum],max([cvmivenname]) as
[cvmivenname],max([dMdate]) as [dMdate],sum([iMassDate]) as [iMassDate],max([cMassUnitName]) as [cMassUnitName],max([dVDate]) as [dVDate],[cPosCode],max([cPosName]) as [cPosName],max([有效期])
as [有效期] FROM

(

SELECT cWhCode,cWhName,cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,cInvCName,cInvM_Unit,cInvA_Unit,cBatch,cvmivencode,cvmivenname,dMdate,iMassDate,cMassUnitName,dVDate,dValidateDate AS
有效期,cPosCode,cPosName,cFree1 , cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10,
cInvDefine1,cInvDefine2,cInvDefine3,cInvDefine4,cInvDefine5,cInvDefine6,cInvDefine7,cInvDefine8,cInvDefine9,cInvDefine10,
cInvDefine11,cInvDefine12,cInvDefine13,cInvDefine14,cInvDefine15,cInvDefine16,(CASE WHEN iGroupType = 0 THEN NULL
WHEN iGroupType = 1 THEN AVG(iChangRate)
WHEN iGroupType = 2 THEN CASE WHEN ROUND(SUM(CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iNum,0) ELSE -ISNULL(iNum,0) END),6) <> 0 THEN SUM(CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iQuantity,0) ELSE
-ISNULL(iQuantity,0) END)/SUM(CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iNum,0) ELSE -ISNULL(iNum,0) END) ELSE NULL END
ELSE NULL END)
as iExchRate,
Round(SUM(CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iQuantity,0) ELSE -ISNULL(iQuantity,0) END ),6) AS iQtty,
Round(SUM( CASE WHEN iGroupType = 0 THEN 0 WHEN iGroupType = 2 THEN (CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iNum,0) ELSE -ISNULL(iNum,0) END)
WHEN iGroupType = 1 THEN (CASE WHEN CS.bRdFlag = 1 THEN iQuantity/ iChangRate ELSE -iQuantity/ iChangRate END) END),6) AS iNum
FROM

(

SELECT DISTINCT CS.AutoID, CS.RdsID, CS.RdID, CS.cWhCode,W.cWhName, CS.cPosCode AS cPosCode,
Position.cPosName AS cPosName, CS.cInvCode, CS.cBatch,isnull(cs.cvmivencode,N'') as cvmivencode,v1.cvenabbname as cvmivenname ,
CS.dMadeDate AS dMDate, CS.iMassDate AS iMassDate,
CS.cMassUnit, isnull(E.enumname,N'') AS cMassUnitName, CS.dVDate, (case when isnull(I.binvquality,0) = 0 then convert(nvarchar(10),N'') else case when isnull(CS.cMassUnit,N'0')=N'0' or
isnull(CS.cmassunit,N'0') =N'' or isnull(CS.cmassunit,N'0')=N'3' then convert(nvarchar(10),dateadd(day,-1,CS.dvdate),121 ) else convert(nvarchar(7),dateadd(month,-1,CS.dvdate),121 ) end end)
as dValidateDate, CS.iQuantity, CS.iNum, CS.cMemo, CS.cHandler,CS.dDate, CS.bRdFlag, CS.cSource,
CS.cFree1, CS.cFree2, CS.cFree3, CS.cFree4, CS.cFree5, CS.cFree6,CS.cFree7, CS.cFree8, CS.cFree9, CS.cFree10,
CS.cAssUnit, CS.cBVencode,I.cInvAddCode, I.cInvName,I.cInvStd,
I.cInvDefine1,I.cInvDefine2,I.cInvDefine3,I.cInvDefine4,I.cInvDefine5,I.cInvDefine6,I.cInvDefine7,I.cInvDefine8,I.cInvDefine9,I.cInvDefine10,
I.cInvDefine11,I.cInvDefine12,I.cInvDefine13,I.cInvDefine14,I.cInvDefine15,I.cInvDefine16,CASE WHEN I.iGroupType = 0 THEN NULL
WHEN I.iGroupType = 2 THEN (CASE WHEN CS.iQuantity = 0.0 OR CS.iNum = 0.0 THEN NULL ELSE CS.iQuantity/CS.iNum END)
WHEN I.iGroupType = 1 THEN CU_G.iChangRate END AS iExchRate
, I.cInvCCode AS cInvCCode, I.iGroupType, CU_M.cComUnitName AS cInvM_Unit, CASE WHEN I.iGroupType = 0 THEN NULL
WHEN I.iGrouptype = 2 THEN CU_A.cComUnitName
WHEN I.iGrouptype = 1 THEN CU_G.cComUnitName END
AS cInvA_Unit, CU_G.iChangRate,
InventoryClass.cInvCName AS cInvCName
FROM Warehouse W with (nolock) RIGHT OUTER JOIN dbo.InvPosition CS with (nolock) ON W.cWhCode = CS.cWhCode LEFT OUTER JOIN ComputationUnit CU_A RIGHT
OUTER JOIN dbo.Inventory I ON CU_A.cComunitCode = I.cAssComUnitCode LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode LEFT OUTER JOIN ComputationUnit CU_G ON
I.cSTComUnitCode = CU_G.cComUnitCode 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
WHERE (1=1) AND 1=1 AND 1=1 And ((I.cInvCode >= N'0100003') And (I.cInvCode <= N'0100003'))

)

AS CS
GROUP BY cWhCode,cWhName,cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,cInvCName,cInvM_Unit,cInvA_Unit,cBatch,cvmivencode,cvmivenname,dMdate,iMassDate,cMassUnitName,dVDate,dValidateDate
,cPosCode,cPosName,cFree1 , cFree2, cFree3, cFree4, cFree5, cFree6, cFree7, cFree8, cFree9, cFree10,
cInvDefine1,cInvDefine2,cInvDefine3,cInvDefine4,cInvDefine5,cInvDefine6,cInvDefine7,cInvDefine8,cInvDefine9,cInvDefine10,
cInvDefine11,cInvDefine12,cInvDefine13,cInvDefine14,cInvDefine15,cInvDefine16,iGroupType

)subtable

GROUP BY [cWhCode], [cInvCode], [cInvStd], [cFree2], [cPosCode]

)subtable

WHERE (1 = 1) ORDER BY [cWhCode], [cInvCode], [cInvStd], [cFree2], [cPosCode]

___________________________________________________________________
Simply Packed:

SELECT cWhCode,cWhName,cInvCode,cInvAddCode,cInvName,cEnglishName,cInvStd,cPackingType,cInvCCode,cInvCName,cInvM_Unit,cPosCode,cPosName,

SUM(CASE WHEN CS.bRdFlag = 1 THEN ISNULL(iQuantity,0) ELSE -ISNULL(iQuantity,0) END ) AS iQtty


FROM


(


SELECT DISTINCT IP.AutoID, IP.RdsID, IP.RdID, IP.cWhCode,W.cWhName, IP.cPosCode AS cPosCode,

Position.cPosName AS cPosName, IP.cInvCode,



IP.cMassUnit, IP.iQuantity, IP.bRdFlag,



I.cInvAddCode, I.cInvName,I.cInvStd, I.cEnglishName, I.cPackingType,

I.cInvCCode AS cInvCCode, CU_M.cComUnitName AS cInvM_Unit,

InventoryClass.cInvCName AS cInvCName

FROM Warehouse W with (nolock) RIGHT OUTER JOIN dbo.InvPosition IP with (nolock) ON W.cWhCode = IP.cWhCode LEFT OUTER JOIN ComputationUnit CU_A RIGHT

OUTER JOIN dbo.Inventory I ON CU_A.cComunitCode = I.cAssComUnitCode LEFT OUTER JOIN dbo.ComputationUnit CU_M ON I.cComUnitCode = CU_M.cComunitCode LEFT OUTER JOIN ComputationUnit CU_G ON

I.cSTComUnitCode = CU_G.cComUnitCode ON IP.cInvCode = I.cInvCode

LEFT JOIN Position ON IP.cPosCode=Position.cPosCode LEFT JOIN InventoryClass ON InventoryClass.cInvCCode=I.cInvCCode






)


AS CS

GROUP BY cWhCode,cWhName,cInvCode,cInvAddCode,cInvName,cInvStd,cInvCCode,cInvCName,cInvM_Unit

,cPosCode,cPosName,cEnglishName,cPackingType