Historical Stock Qty

Posted by chris allsopp on December 18, 2006, 7:57 am

Please log in for more thread options

Is there a way (or indeed does RMS hold) of getting historical stock level
data, that is, we have 20 in stock today, but what was our stock level on
the 14th November ?

Chris

Posted by Rick Feuling [R on December 18, 2006, 9:06 am

Please log in for more thread options

Hi Chris,

RMS definitely stores the required information but it can be a bit difficult
to get at. The item movement report has the data you need but probably not
in the format you hoped for. If you export the report to excel and put a
little work into some formulas you could potentially get your information
without too much effort, the 66,000 row limitation might cause a problem
depending on how much data you’re working with.

We are in the middle of building an advanced purchasing tool that shows
things like number of out of stocks during a given period of time and length
of out of stocks - we use this same information to calculate these figures.

I hope that helps!

Rick Feuling
Retail Information Technology Enterprises (RITE)
http://www.rite.us

"chris allsopp" wrote:

> Is there a way (or indeed does RMS hold) of getting historical stock level

> data, that is, we have 20 in stock today, but what was our stock level on

> the 14th November ?

> Chris

Posted by Nashat on December 19, 2006, 8:00 am

Please log in for more thread options

: quoted-printable

Chris,

=20

try using the attached script, just set the date and time u want and =
u'll get the stock u had in that date and time.

=20

notes: -

1.. I tried doing this query using derived tables but the performance =
was extremely bad.
2.. I couldn't make it using views
3.. i made it using temporary tables, but i got a bit better =
performance with using normal tables
4.. I couldn't make it in a report coz i couldn't pass the date while =
creating the views in PreQuery1 and PreQuery2
If anyone has any enhancements or ideas concerning improving this script =
and use it as a report, plz do

=20

> Is there a way (or indeed does RMS hold) of getting historical stock =

level=20

> data, that is, we have 20 in stock today, but what was our stock level =

on=20

> the 14th November ?

>=20

> Chris=20

>=20

------=_NextPart_001_0047_01C7237E.64C804F0
Content-Type: text/html;
 charset="windows-1256"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dwindows-1256">
<META content=3D"MSHTML 6.00.2900.3020" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV>
<SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">Chris,<SPAN=20
style=3D"FONT-FAMILY: 'Arial Unicode MS'; mso-bidi-language: =
AR-SA"><?xml:namespace=20
prefix =3D o ns =3D "urn:schemas-microsoft-com:office:office"=20
/><o:p></o:p>
<FONT=20
color=3D#000000><FONT=20
face=3D"Times New Roman"> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">try using=20
the attached script, just set the date and time u want and u'll get =
the=20
stock u had in that date and time.<o:p></o:p>
<FONT=20
color=3D#000000><FONT=20
face=3D"Times New Roman"> <o:p></o:p>
<SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">notes:=20
-<o:p></o:p>
<OL type=3D1>
<LI class=3DMsoNormal=20
style=3D"MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; =
mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo1; tab-stops: list =
36.0pt"><SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">I tried=20
doing this query using derived tables but the performance was=20
extremely bad.<o:p></o:p></LI>
<LI class=3DMsoNormal=20
style=3D"MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; =
mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo1; tab-stops: list =
36.0pt"><SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">I=20
couldn't make it using views<o:p></o:p></LI>
<LI class=3DMsoNormal=20
style=3D"MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; =
mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo1; tab-stops: list =
36.0pt"><SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">i made=20
it using temporary tables, but i got a bit better performance with =
using=20
normal tables<o:p></o:p></LI>
<LI class=3DMsoNormal=20
style=3D"MARGIN: 0cm 0cm 0pt; mso-margin-top-alt: auto; =
mso-margin-bottom-alt: auto; mso-list: l0 level1 lfo1; tab-stops: list =
36.0pt"><SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">I=20
couldn't make it in a report coz i couldn't pass the date while =
creating the=20
views in PreQuery1 and PreQuery2<o:p></o:p></LI></OL>
<SPAN=20
style=3D"FONT-SIZE: 10pt; COLOR: maroon; FONT-FAMILY: 'Century =
Gothic'">If anyone=20
has any enhancements or ideas concerning improving this script and use =
it as a=20
report, plz do<o:p></o:p>
<FONT=20
color=3D#000000><FONT=20
face=3D"Times New =
Roman"> <o:p></o:p></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"chris allsopp" <<A=20
size=3D2>chris@emmaallsopp.plus.com</A>> wrote=20
in message <A =
face=3DArial =
face=3DArial size=3D2>...</DIV>> =
Is there a way=20
(or indeed does RMS hold) of getting historical stock level > =
data, that=20
is, we have 20 in stock today, but what was our stock level on > =
the 14th=20
November ? > > Chris > ></BODY></HTML>

------=_NextPart_001_0047_01C7237E.64C804F0--

------=_NextPart_000_0046_01C7237E.64C804F0
Content-Type: application/octet-stream;
 name="Previous date2.sql"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
 filename="Previous date2.sql"

if exists(select * from sysobjects where name=3D't1') drop table t1
if exists(select * from sysobjects where name=3D't2') drop table t2

declare @myDate datetime
--please set here the date and time on which u want to know your stock
set @MyDate=3D'2006-01-01 00:00:00'

SELECT distinct Department.name AS Department, Category.name AS =
Category,Itemlookupcode,Item.Description
,item.id as ItemID
,Item.Quantity-isnull(Sum(InventoryTransferLog.Quantity),0) as QtyGross
into t1
FROM Item left JOIN Department ON item.departmentid =3D department.id=20
left join category ON item.categoryid =3D category.id=20
LEFT JOIN InventoryTransferLog ON Item.ID=3DInventoryTransferLog.ItemID=20
where datetransferred > @MyDate
GROUP BY all =
Department.name,category.name,item.itemlookupcode,Item.Description,item.q=
uantity,Item.ID

select item.id as itemid,isnull(sum(TransactionEntry.Quantity),0) as =
QtySales
into t2
from item left join TransactionEntry on item.id=3Ditemid
left join [transaction] on =
[Transaction].Transactionnumber=3DTransactionentry.Transactionnumber
and time > @Mydate
group by item.id=20

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