Hi,
In the query below, the date criteria is hardcode. If I want to run the query in SAP B1 and give the From Date and To Date option, then what should be done?
CREATE TABLE #EA1
(ITMSGRPNAM VARCHAR(30),
ITEMCODE VARCHAR(20),
DSCRIPTION VARCHAR(100),
IssueQTY DECIMAL(20,4),
IssueAMT DECIMAL(20,4),)
ALTER TABLE #EA1 ALTER COLUMN ITMSGRPNAM VARCHAR(30)COLLATE SQL_Latin1_General_CP850_CI_AS
ALTER TABLE #EA1 ALTER COLUMN ITEMCODE VARCHAR(20)COLLATE SQL_Latin1_General_CP850_CI_AS
ALTER TABLE #EA1 ALTER COLUMN DSCRIPTION VARCHAR(100)COLLATE SQL_Latin1_General_CP850_CI_AS
INSERT INTO #EA1
SELECT T2.ITMSGRPNAM,T0.ITEMCODE,T0.DSCRIPTION, SUM(T0.QUANTITY) "Issue Qty",SUM(T0.LINETOTAL)"Issue Amt."
FROM WTR1 T0
INNER JOIN OITM T1 ON T0.ITEMCODE=T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD
INNER JOIN OWTR T3 ON T0.DOCENTRY=T3.DOCENTRY
Where T3.DocDate between '20111101' and '20111130'
GROUP BY T0.ITEMCODE,T0.DSCRIPTION,T2.ITMSGRPNAM
ORDER BY T2.ITMSGRPNAM
CREATE TABLE #EA2
(ITMSGRPNAM VARCHAR(30),
ITEMCODE VARCHAR(20),
DSCRIPTION VARCHAR(100),
CONSQTY DECIMAL(20,4),
SBQTY DECIMAL (20,4))
ALTER TABLE #EA2 ALTER COLUMN ITMSGRPNAM VARCHAR(30)COLLATE SQL_Latin1_General_CP850_CI_AS
ALTER TABLE #EA2 ALTER COLUMN ITEMCODE VARCHAR(20)COLLATE SQL_Latin1_General_CP850_CI_AS
ALTER TABLE #EA2 ALTER COLUMN DSCRIPTION VARCHAR(100)COLLATE SQL_Latin1_General_CP850_CI_AS
INSERT INTO #EA2
SELECT T2.ITMSGRPNAM, T0.ITEMCODE, T1.ItemName , SUM ( T0.PLANNEDQTY) "SB QTY",SUM(T0.IssuedQty )AS 'CONSUMPTION QTY'
FROM WOR1 T0
INNER JOIN OITM T1 ON T0.ITEMCODE=T1.ITEMCODE
INNER JOIN OITB T2 ON T1.ITMSGRPCOD=T2.ITMSGRPCOD
INNER JOIN OWOR T3 ON T0.DOCENTRY=T3.DOCENTRY
INNER JOIN OITW T4 ON T1.DfltWH = T4.WhsCode
Where t1.ItemCode = t4.ItemCode and T3.PostDate between '20111101' and '20111130'
GROUP BY T1.ITMSGRPCOD,T2.ITMSGRPNAM, T0.ITEMCODE,T1.ItemName ORDER BY T2.ITMSGRPNAM
SELECT A.ITMSGRPNAM ,A.ITEMCODE , A.DSCRIPTION, B.IssueQTY AS 'TRANSFER FROM STORES' ,A. SBQTY AS 'SHOULD BE CONSUMPTION', A.CONSQTY AS 'ACTUAL CONSUMPTION' FROM #EA2 A , #EA1 B
WHERE A.ITEMCODE = B.ITEMCODE
ORDER BY A.ITMSGRPNAM
DROP TABLE #EA1
DROP TABLE #EA2
Rgds
Kalli