22,209
社区成员
发帖
与我相关
我的任务
分享
WHERE M.whscode =@WHSCODE
AND M.ItemCode LIKE '' + @ITEMCODE + '%' AND M.docdate BETWEEN @DATEFROM AND @DATETO
AND M.型号 IS NOT NULL
DECLARE @WHSCODE VARCHAR(20)
,@ITEMCODE VARCHAR(30)
,@Codebars VARCHAR(30)
,@datefrom DATETIME
,@dateto DATETIME
,@SQL NVARCHAR(4000)
,@type NVARCHAR(4)
SET @DATEFROM='2010-01-01'
SET @DATETO='2011-01-01'
SET @WHSCODE='1-AZJT01'
SET @ITEMCODE='A'
SET @SQL = ''
DECLARE CUR1 CURSOR FOR --获取型号列值,作为分组依据
SELECT DISTINCT M.型号
FROM
(
SELECT d.ItemCode
,d.WhsCode
,SUBSTRING(T4.[BatchNum],4,2) AS '型号'
FROM dbo.OIGN AS tt
INNER JOIN dbo.IGN1 AS d ON tt.DocEntry = d.DocEntry AND tt.CurSource = 'C'
INNER JOIN ibt1 T4 ON T4.BaseType = D.ObjType AND D.DocEntry=T4.BaseEntry AND D.LineNum=t4.BaseLinNum
WHERE ISNULL(SUBSTRING(T4.[BatchNum],4,2),'')<>'' AND tt.docdate BETWEEN @DATEFROM AND @DATETO
) M
LEFT JOIN OITM P on P.ITEMCODE = M.itemcode
INNER JOIN owhs T5 on M.whscode = T5.whscode
WHERE M.whscode =@WHSCODE
AND M.ItemCode LIKE '' + @ITEMCODE + '%'
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @type
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql= @sql + ',[' + @type + ']'
FETCH NEXT FROM CUR1 INTO @type
END
SET @sql = STUFF(@sql,1,1,'')
CLOSE CUR1
DEALLOCATE CUR1
SELECT @sql
SET @type = ''