Stock on hand at a point in time

[Former Member]

Hi Experts

I am busy doing an inventory report in Crystal. I need to pull the stock on hand for all the items for each month (past 4 years, +-2500 items)

At the moment, i am taking inqty - outqty where date <= (beginning of each month)

My report is taking forever to process the info.

Is there any other way I can get the stock on hand at previous points in time? Or any tricks to help SQL process the data quicker?

[Former Member]

Hi Malhaar

If you could send that query, I would really appreciate it.

As far as the values go, you just need to sum up the Transvalue where do date <= (date in which you want to calculate for)

You can use the oivl Table or the oinm view

[former_member218051]

Hi Bradely,

I've a query pulling opening stock in qty from the system.

Through unions on all the transaction tables i'm pulling the data warehousewise.

It hardly takes any time.

I'm trying to print stock value too. Which I'm unable to figure out.

If you want I'll pass that query.

Any idea about how to figure out stock value ?

Thanks

Malhaar

[Former Member]

Hi Bradley...........

What and how many joins you have in your query report?

This generally happens due to unwanted joins.....

Regards,

Rahul

[Former Member]

Hi Gordon

Thanks, that's a very good idea. Otherwise I will create a view in SQL itself.

[Former Member]

Create a view is simple and easy to be implement. However, it may not help the performance.

[Former Member]

Hi,

You may try create an UDT to run each month to get history data to the table. So that you do not need to run it again and again.

Thanks,

Gordon