SQL Query

[Former Member]

Good day, I have an issue with the SQL query results. I created a table where in all related item transactions in SAP will insert to this table. Here's the Column of my created table.

From this table, i wanted to create a report that shows only the remaining quantity of the item on its specific date, warehouse and location. Here's my second query:

I wanted that the issued item should offset the quantity of the first line item of 5 then if it exceed, it will go to the second one then third and so on. The date is the problem why its hard to get my requirement. Is there any one who knows how to resolve this issue? I really need your help.

I don't need any work around in SAP, I just need to do it in SQL query. Please help.

Thank you!!..

Regards,

Ardie

[Rafaee_Mustafa]

Hi Ardie,

You wan to knockoff your issued quantity and thos who knocked off it should not be displayed is the result ??

Its hard to achive that through query Why dont you catter this through Crystal Report (running total) or the tool you are using ??

[Former Member]

Yes Sir Rafaee, the first line should not be displayed since it was already been exhausted. I was thinking if this could be possible using only a query in SQL. But It seems close to impossible and only crystal can cater this and it sounds easy. Do you have any suggestion Sir on how to do it in crystal report? Can you give me some guide on how to achieve my goal. Thank you Sir.

Regards,

Ardie

[Former Member]

Hey Ardie,

my suggestion is to select your  data result into a temporary table. On this temp table you run the query to knockoff the issued quantity etc..

Regards Steffen

[Rafaee_Mustafa]

I have nothing to test on right now.

Fetch the simple query and try to set this through Running Total

and supress the detail section where  Quantity =0

[Former Member]

Thank you for your suggestion Sir Steffen, I really appreciated it but I have a question. If I knockoff all issued quantity, remaining items will be the issued. How can I offset all the receiving quantity over the issuance and the remaining results will be the quantity on the latest date?

Regards,

Ardie

[Former Member]

How can I do that? The date is still the issue. If I run a simple query showing all the transactions, it will appear all the receiving and issuance of an item. Then how could I knockoff all the issued items with a corresponding effect on the receiving items then leaving only the remaining item with the latest date?

Thank you!..

Regards,

Ardie

[Former Member]

Could you please post your query, for the first data set. i wanne try to solve your issue

[Former Member]

Here's my query selecting all transaction on the specific itemcode,location and warehouse.

And this is my expected result if I make a query.

[Former Member]

Hi,

  you may try this...

SELECT DISTINCT(ITEMCODE) AS ITEMCODE,DATE,

MAX(DESCRIPTION) AS ITEMNAME,MAX(WAREHOUSE) AS WAREHOUSE,MAX(LOCATION) AS LOCATION,SUM(QUANTITY) AS QUANTITY

FROM LOCATION WHERE LOCATION='ABC'

GROUP BY DATE

[former_member209066]

Hi,

Select * From OINM A INNER JOIN OLCT B ON A.Location=B.Code

Where A.ItemCode='[%1]' and A.Warehouse='[%2]' and B.Location='[%3]'

Thanks,

Nthi

[KennedyT21]

HI Ardie Trinidad ,

Use Order by in the SQl,

Order By Date,Quantity

Hope Help you.

Regards

Kennedy

[Former Member]

Hi Sir, Nothing will change if I follow your recommendation. It will only ordered by date. I think it needs a little trick in SQL query but I don't know how to do it. If I will not consider the date, the report is fine but of course if I include the date in the report it will spread out all the transactions including issuance of an item which should be offset to the 1st line and to the second line and so on if the previous lines have been exhausted.

[KennedyT21]

HI  Ardie Trinidad

In the query Use---

select MAx(Date) .....

and remove in  group by( date)

Regards

Kennedy

[Former Member]

Hi Sir, Using Max or Min will only results to one date only. My expected results is like this:

Take note the date. If I use Max (DATE), both date will be 8/2/12.

Thanks you.

Regards,

Ardie

[KennedyT21]

Hi Ardie,

In your case the item name is different so sure it will not.

But the above query if suppose you have multiple entry in different dates then max date only retrieve in the Query.

Regards

Kennedy

[Former Member]

The item name is the same, also the warehouse and the location. The only difference is the date. If I use the Max date, the date will only be the max (date). I was thinking if the date of issuance of goods can be changed using the date of first in date of an item so that the amount will be offset. I cannot use the Max (date). I need the complete remaining date of an items.

[Former Member]

This is my expected result: