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

[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.