Skip to Content

Date Selection in Query

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Dec 06, 2011 at 10:37 AM

    Hi

    you can try this

    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 '[%0]' and '[%1]'
    
    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 '[%2]' and '[%3]'
    
    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
    

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Fasolis,

      We had prepared the query in SQL. The dates here are hard coded. When i run the same query thru SAP query generator the results are based on thehardcoded dates.

      If I want to check the results again for another period range in SAP, then I have to change the dates and run the query again.

      Changing the dates in your query for getting the result is not the correct practise.

      So when I run the said in SAP B1, I want to have dates as selection criteria i.e From Posting Date to TO posting Date.

      I hope you have understood what Iam trying to convery.

  • author's profile photo Former Member
    Former Member
    Posted on Dec 06, 2011 at 12:43 PM

    Hi Kalli,

    Are you sure the temp tables are mandatory? Whenever it is possible, you should avoid them. Tell me your goal to achieve.

    Thanks,

    Gordon

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.