Skip to Content
0
Dec 06, 2011 at 10:33 AM

Date Selection in Query

898 Views

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