Inventory value on a specific Date

.........or was I supposed to come into work on Jan 1 and run that inventory value report?

Mickie wrote:

.........or was I supposed to come into work on Jan 1 and run that inventory value report?

Mickie wrote: > Any way to get inventory value on a date, like Dec 31st? > > I need it for taxes. > >

try the attached script, i think it does what u need, i recommend that u run this script while your store is closed or at least while no transactions are being posted

begin 666 Previous date2.sql M:68@97AI2Y1=6%N=&ET>2DL,"D@87,@471Y4V%L97,-"FEN=&\@5#(- M"F9R;VT@271E;2!L969T(&IO:6X@5')A;G-A8W1I;VY%;G1R>2!O;B!I=&5M M+FED/6ET96UI9 T*;&5F="!J;VEN(%MT

I am looking not just for inventory count but value as of a certain date. Can that be figured into the query you posted?

PERFECT!!!

Nashat wrote:

Chris,

It's hard to get the information you ask for, the stock is easily calculated coz we do have the movement history for all items, but the cost is not the same issue, we might look at the TransactionEntry table to know the cost of the sold items in that day, but u definitely don't have sales history for all items in every day, so we'd be getting the correct cost of SOME items in that day by looking into TransactionEntry table but we wouldn't be getting correct cost figures for MOST of the items that we have nothing about them except their current cost.

but didn't have the time to test it yet.

--------------------------------------------------------------------------------

AS QTY, MAX(t1.Price) AS SalePrice

ELSE NULL END AS TransactionNumber,

JOIN Category ON Item.CategoryID = Category.ID

InventoryTransferLog.StoreID = Serial.StoreID

JOIN Category ON Item.CategoryID = Category.ID

Serial.StoreID = Store.ID

--------------------------------------------------------------------------------

thanks Nashat but there is nothing attached maybe you could e-mail it snipped-for-privacy@jmmpr.com

i think it's the same concept.

it, and please note too that Julien's cost wouldn't be correct since it's the current cost not the historical one

date, but didn't have the time to test it yet.

--------------------------------------------------------------------------------

SUM(t1.Quantity) AS QTY, MAX(t1.Price) AS SalePrice

ELSE NULL END AS TransactionNumber,

JOIN Category ON Item.CategoryID = Category.ID

AND InventoryTransferLog.StoreID = Serial.StoreID

JOIN Category ON Item.CategoryID = Category.ID

= [Transaction].TransactionNumber AND [Transaction].StoreID = Store.ID

Serial.StoreID = Store.ID

--------------------------------------------------------------------------------

sorry i forgot:)

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

begin 666 Previous date2.sql M:68@97AI2Y1=6%N=&ET>2DL,"D@87,@471Y4V%L97,-"FEN=&\@5#(- M"F9R;VT@271E;2!L969T(&IO:6X@5')A;G-A8W1I;VY%;G1R>2!O;B!I=&5M M+FED/6ET96UI9 T*;&5F="!J;VEN(%MT

thanks i ran the sql from store admin but all i got was

Query executed successfully:

"Nashat" wrote:

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

thanks i ran the sql from store admin but all i got was

Query executed successfully:

hozay" wrote:

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

strange!, it works in Query Analyzer but in Administrator it dt doesn't! anyways, all what u need to do is to make a new query and paster the last statement and run it:-

select department,category,itemlookupcode,description,qtygross+qtysales as Quantity from t1 inner join t2 on t1.itemid=t2.itemid order by department,category,itemlookupcode

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------